﻿/*********************************************
 创建者：  曹亚亮
 创建时间：2017-05-13
 功能说明：配货作业
 ********************************************/
using com.nm.ApiServer;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using com.nm.Helper;
using com.nm.library.Helper;
using DistributeAPI.Helper;
using DistributeAPI.Model;
using george.Data;
using george.Helper;
using NHibernateVertica;
using Vertica.Data.VerticaClient;
using com.nm.db.Models.Hkerp;
using com.nm.library;
using com.nm.models;
using george.Web.Helper;
using VerticaAPI;

namespace DistributeAPI
{
    [Description("配货作业")]
    public class PickingOperationsHandler : BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();

        #region 生成配货单号API

        [MustLogin]
        [Description("获取配货单号API")]
        [ReturnType(typeof(PaperData<PickingOperationsModel.GetTaskBillNoModel>))]
        public ApiResult GetTaskBillNo([ParamDescription("配货类型：（1 可用库存 2 成品外发到货通知单）")] int AllotType)
        {
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var strTaskBillNo = string.Empty;
                var strYear = DateTime.Now.ToString("yy");
                var strMonth = DateTime.Now.ToString("MM");
                var strDate = DateTime.Now.ToString("dd");
                var strAllotType = "(" + AllotType + ")";

                strTaskBillNo = strYear + strMonth + strDate + strAllotType + UserInfo.UserID + "-";
                var oddNum = IsTaskBillNo(strTaskBillNo);
                if (string.IsNullOrEmpty(oddNum))
                {
                    strTaskBillNo = strTaskBillNo + "001";
                }
                else
                {
                    //获取单日员工操作的最大流水号加1
                    var intNum = Convert.ToInt32(oddNum.Split('-')[1].ToString()) + 1;

                    strTaskBillNo = strTaskBillNo + intNum.ToString().PadLeft(3, '0');
                }

                var model = new PickingOperationsModel.GetTaskBillNoModel { TaskBillNo = strTaskBillNo };
                return Success(model);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        //作者：曹亚亮
        //时间：2017-05-17
        /// <summary>
        /// 判断操作员在当天是否以创建过单号
        /// </summary>
        /// <param name="TaskBillNo">单号前缀</param>
        /// <returns>返回空字符为：未创建 不为空以创建过</returns>
        public string IsTaskBillNo(string TaskBillNo)
        {

            try
            {
                var strOddNum = string.Empty;
                var sql =
                    $@" SELECT  MAX(TaskBillNo)
FROM    ngoods.ngoods_DC_TaskMaster
WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
        AND CompanyID = '{UserInfo.UserCompanyId}'
        AND TaskBillNo LIKE '{TaskBillNo}%'  ";

                var cmd = Helper.GetSqlStringCommand(sql);
                var oddNum = Helper.ExecuteScalar(cmd);

                if (oddNum != null)
                {
                    strOddNum = Convert.ToString(oddNum);
                    return strOddNum;
                }
                else
                {
                    return strOddNum;
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion

        #region 分配虚拟仓

        [MustLogin]
        [Description("根据通知单号获取恒康虚拟库存API")]
        [ReturnType(typeof(PaperData<PickingOperationsModel.NoticeNoStockIDModel>))]
        public ApiResult GetPMArrNoticeList([ParamDescription("通知单号")] string BillNo)
        {
            if (string.IsNullOrEmpty(BillNo))
            {
                return Error("单号不能为空");
            }
            if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
            {
                return Error("该用户不属于任何公司!");
            }
            if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
            {
                return Error("该用户所在公司没有任何数据源!");
            }
            try
            {
                var data = new PickingOperationsModel.NoticeNoStockIDModel();
                var list = new List<PickingOperationsModel.PMArrNoticeModel>();
                switch (UserInfo.UserExtrSource)
                {
                    case "KM":
                        list = KM_PMArrNoticeList(BillNo).ToList();
                        break;
                    default:
                        return Error("除KM品牌之外,此功能暂未实现!");
                        break;
                }

                var strCompanyID = string.Empty;
                var strSourceBillNo = string.Empty;
                if (list != null && list.ToList().Count > 0)
                {
                    strCompanyID = list.ToList()[0].CompanyID;
                    strSourceBillNo = list.ToList()[0].BillNo;
                }

                var dtStockID = GetStockIDList(strCompanyID, strSourceBillNo);
                var dtStockIDList = ModelConvertHelper<PickingOperationsModel.StockIDListModel>.ConvertToModel(dtStockID);
                data.StockIDList = dtStockIDList;
                data.List = list;
                return Success(data);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }
        //作者：ljj
        //时间：2017-07-26
        public IList<PickingOperationsModel.PMArrNoticeModel> KM_PMArrNoticeList(string BillNo)
        {
            try
            {
                var companyID = UserInfo.UserCompanyId;
                var db = new HKERPEntities();
                var result = db.KM_V_PMArrNotice.Where(t => t.BillNo == BillNo && t.CompanyID == companyID)
                    .Select(o => new PickingOperationsModel.PMArrNoticeModel
                    {
                        CompanyID = o.CompanyID,
                        CNStateName = o.CNStateName,
                        SourceBillNo = o.BillNo,
                        VendCustID = o.VendCustID,
                        VendCustName = o.VendCustName,
                        StockID = "",
                        StockName = "",
                        MaterialID = o.MaterialID,
                        SizeID = o.SizeID,
                        SizeName = o.SizeName,
                        TotalQty = o.Qty,
                        BillNo = o.SourceBillNo,
                        SizeCodeSort = o.Remark2,
                        ExtrSource = UserInfo.UserExtrSource,
                    });

                foreach (var item in result)
                {
                    if (item.SizeCodeSort.Length == 1)
                    {
                        item.SizeCodeSort = item.SizeCodeSort.PadLeft(2, '0');
                    }
                }

                var temp = result.OrderBy(g => g.SourceBillNo).ThenBy(g => g.MaterialID).ThenBy(g => g.SizeCodeSort);
                return temp.ToList();
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        [MustLogin]
        [Description("根据仓库ID和货号ID获取虚拟分仓计算的初始分配数据API")]
        [ReturnType(typeof(PaperData<PickingOperationsModel.GetDCVirtualStockModel>))]
        public ApiResult GetFirstAllotList([ParamDescription("仓库ID如：KM000894,KM000083")] string StockID, [ParamDescription("货号ID")] string MaterialID)
        {
            if (string.IsNullOrEmpty(StockID))
            {
                return Error("仓库ID不能为空");
            }
            if (string.IsNullOrEmpty(MaterialID))
            {
                return Error("货号ID不能为空");
            }
            if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
            {
                return Error("该用户不属于任何公司!");
            }
            if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
            {
                return Error("该用户所在公司没有任何数据源!");
            }
            StockID = "'" + StockID.Replace("，", ",").TrimEnd(',').TrimStart(',').Replace(",", "','") + "'";

            try
            {
                var dt = GetFirstAllot(StockID, MaterialID);
                var list = ModelConvertHelper<PickingOperationsModel.GetDCVirtualStockModel>.ConvertToModel(dt);
                return Success(list);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        [MustLogin]
        [JsonType]
        [Description("保存手动调整分配的虚拟仓库存结果API")]
        [ReturnType(typeof(PaperData<PickingOperationsModel.GetDCVirtualStockOrderModel>))]
        public ApiResult InsertDCVirtualStock([ParamDescription("需要保存分配结果的实体类列表")] PickingOperationsModel.DCVirtualStockModelList param)
        {
            try
            {
                if (param == null) return Error("参数对象为空");
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var data = new PickingOperationsModel.GetDCVirtualStockOrderModel();
                var eom = new ErpOrderHelper.ErpOrderMessage();
                eom = Insert_DC_VirtualStock(param.List, param.UserID, param.MaBillNo);
                data.List = null;
                if (eom != null && eom.RetVal == 0)
                {
                    data.RetVal = eom.RetVal;
                    data.Message = "生成调拨申请单(未生效)成功";
                    data.List = ModelConvertHelper<PickingOperationsModel.EepBillNoModel>.ConvertToModel(eom.BillNo);
                }
                else if (eom != null && eom.RetVal == 1)
                {
                    data.RetVal = eom.RetVal;
                    data.Message = "无有效数据，无法生成ERP单据";
                    data.List = null;
                }
                else if (eom != null && eom.RetVal == 2)
                {
                    data.RetVal = eom.RetVal;
                    data.Message = "该到货通知单下的此货号以进行虚拟仓分配";
                    data.List = null;
                }
                else if (eom != null && eom.RetVal == 3)
                {
                    data.RetVal = eom.RetVal;
                    data.Message = "进行虚拟仓分配的数据记录数不可为空";
                    data.List = null;
                }
                else
                {
                    data.RetVal = -1;
                    data.Message = "生成ERP单据失败";
                    data.List = null;
                }

                return Success(data);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        [MustLogin]
        [Description("根据单号和货号ID获取带分配状态的虚拟库存数据API")]
        [ReturnType(typeof(PaperData<PickingOperationsModel.IsAllotDCVirtualStockModel>))]
        public ApiResult GetDCVirtualStock([ParamDescription("单号")] string BillNo, [ParamDescription("货号ID")] string MaterialID)
        {
            if (string.IsNullOrEmpty(BillNo))
            {
                return Error("单号不能为空");
            }
            if (string.IsNullOrEmpty(MaterialID))
            {
                return Error("货号ID不能为空");
            }
            if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
            {
                return Error("该用户不属于任何公司!");
            }
            if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
            {
                return Error("该用户所在公司没有任何数据源!");
            }
            try
            {
                var data = new PickingOperationsModel.IsAllotDCVirtualStockModel();
                data.IsAllot = IsAllot(BillNo, MaterialID);
                var dt = Get_DC_VirtualStock(BillNo, MaterialID);
                var list = ModelConvertHelper<PickingOperationsModel.GetDCVirtualStockModel>.ConvertToModel(dt);
                data.List = list;
                return Success(data);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        //作者：曹亚亮
        //时间：2017-06-12
        /// <summary>
        /// 根据公司代码和ERP的订单号获取默认分配的虚拟仓库ID
        /// </summary>
        /// <param name="CompanyID">公司代码</param>
        /// <param name="SourceBillNo">ERP的订单号</param>
        /// <returns></returns>
        public DataTable GetStockIDList(string CompanyID, string SourceBillNo)
        {
            try
            {
                var sql =
                    $@"SELECT  StockID
FROM    ngoods.ngoods_DC_OrderForStock
WHERE   CompanyID = '{UserInfo.UserCompanyId}'
        AND BillNo = '{SourceBillNo}'
        AND ExtrSource = '{UserInfo.UserExtrSource}' ";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        //作者：曹亚亮
        //时间：2017-05-18
        /// <summary>
        /// 根据仓库ID和货号ID获取虚拟分仓计算的初始分配数据
        /// </summary>
        /// <param name="StockID">仓库ID：如（'KM000894','KM000083','KM001121'）</param>
        /// <param name="MaterialID">货号编号</param>
        /// <returns></returns>
        public DataTable GetFirstAllot(string StockID, string MaterialID)
        {
            try
            {
                var sql = $@"SELECT  DBP.CompanyID ,
        '' AS SourceBillNo ,
        EBS.StockID ,
        EBS.StockName ,
        DBP.MaterialID ,
        DBP.SizeID ,
        SMS.SizeName ,
        SUM(Qty) AS TotalQty
FROM    ngoods.ngoods_DC_Bas_PLOrder DBP ,
        nods.nods_erp_SD_Mat_Size SMS ,
        ndw.ndw_Shop DDS ,
        ndw.ndw_AreaProCity APC ,
        nods.nods_erp_Bas_Stock EBS
WHERE   DBP.ExtrSource = '{UserInfo.UserExtrSource}'
        AND DBP.CompanyID = '{UserInfo.UserCompanyId}'
        AND DBP.SizeID = SMS.SizeID
        AND DBP.ExtrSource = SMS.ExtrSource
        AND DBP.ShopID = DDS.ShopID
        AND DBP.ExtrSource = DDS.ExtrSource
        AND DBP.CompanyID = DDS.CompanyID
        AND DDS.AreaID = APC.AreaID
        AND DBP.ExtrSource = APC.ExtrSource
        AND DBP.CompanyID = APC.CompanyID
        AND APC.StockID = EBS.StockID
        AND DBP.ExtrSource = EBS.ExtrSource
        AND DBP.CompanyID = EBS.CompanyID
        AND EBS.StockID IN ( {StockID} )
        AND DBP.MaterialID = '{MaterialID}'
GROUP BY DBP.CompanyID ,
        SourceBillNo ,
        EBS.StockID ,
        EBS.StockName ,
        DBP.MaterialID ,
        DBP.SizeID ,
        SMS.SizeName ,
        SMS.SizeCodeSort
ORDER BY EBS.StockID ,
        DBP.MaterialID ,
        SMS.SizeCodeSort ";

                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        //作者：曹亚亮
        //时间：2017-05-15
        /// <summary>
        /// 保存手动调整分配的虚拟仓库存结果
        /// </summary>
        /// <param name="list"></param>
        /// <param name="UserID">操作员ID</param>
        /// <param name="MaBillNo">手工单号</param>
        /// <returns></returns>
        public ErpOrderHelper.ErpOrderMessage Insert_DC_VirtualStock(IList<PickingOperationsModel.InsertDCVirtualStockModel> list, string UserID, string MaBillNo)
        {
            try
            {
                var eom = new ErpOrderHelper.ErpOrderMessage();
                if (list != null && list.Any())
                {
                    if (!IsAllot(list[0].SourceBillNo, list[0].MaterialID))
                    {
                        //批量添加分配的虚拟库存
                        var dt = DataTableHelper.LinqToDataTable(list.Select(
                            p => new
                            {
                                CompanyID = UserInfo.UserCompanyId,
                                p.SourceBillNo,
                                p.StockID,
                                p.MaterialID,
                                p.SizeID,
                                p.TotalQty,
                                Operator = UserInfo.UserID,
                                OperateDate = DateTime.Now,
                                ExtrSource = UserInfo.UserExtrSource,
                            }
                        ).ToList());
                        switch (UserInfo.UserExtrSource)
                        {
                            case "KM":
                                return KM_Insert_DC_VirtualStock(dt, UserID, MaBillNo);
                            //todo.暂未实现其他品牌
                            default:
                                return null;
                        }
                    }
                    else
                    {
                        eom.RetVal = 2;
                        eom.BillNo = null;
                        return eom;
                    }
                }
                else
                {
                    eom.RetVal = 3;
                    eom.BillNo = null;
                    return eom;
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// 作者：ljj
        /// 时间：2017-07-26
        public ErpOrderHelper.ErpOrderMessage KM_Insert_DC_VirtualStock(DataTable dt, string UserID, string MaBillNo)
        {
            try
            {
                var delSql =
                    $@"DELETE FROM KM_DC_VirtualStock WHERE CompanyID='{UserInfo.UserCompanyId}' AND SourceBillNo='{dt
                        .Rows[0]
                        ["SourceBillNo"]}' AND isnull(FinishFlag,0)=0";
                var sb = new StringBuilder();
                for (var i = 0; i < dt.Rows.Count; i++)
                {
                    sb.AppendLine(
                        $@"INSERT  INTO KM_DC_VirtualStock
        ( CompanyID ,
          SourceBillNo ,
          StockID ,
          MaterialID ,
          SizeID ,
          TotalQty
        )
VALUES  ( '{dt.Rows[i]["CompanyID"].ToString()}' ,
          '{dt.Rows[i]["SourceBillNo"].ToString()}' ,
          '{dt.Rows[i]["StockID"].ToString()}' ,
          '{dt.Rows[i]["MaterialID"].ToString()}' ,
          '{dt.Rows[i]["SizeID"].ToString()}' ,
          {dt.Rows[i]["TotalQty"].ToString()}
        );");
                }
                var procedureSql =
                    $@"exec [dbo].[KM_DC_VirtualMoveBill] '{UserInfo.UserCompanyId}','{dt.Rows[0]["SourceBillNo"]}','{UserID}','{MaBillNo}' ";
                var updSql =
                    $@"UPDATE  ngoods.ngoods_DC_VirtualStock
SET     FinishFlag = 1
WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
        AND CompanyID = '{UserInfo.UserCompanyId}'
        AND SourceBillNo = '{dt.Rows[0]["SourceBillNo"]}'
        AND ISNULL(FinishFlag, 0) = 0";
                return ErpOrderHelper.ExecuteSqlTran_Task(dt, "ngoods.ngoods_DC_VirtualStock", delSql, sb.ToString(),
                    procedureSql, updSql);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }


        //作者：曹亚亮
        //时间：2017-06-10
        /// <summary>
        /// 根据到货通知单号判断是否已经进行虚拟仓库分配
        /// </summary>
        /// <param name="BillNo">到货通知单号</param>
        /// <returns>true：已分配 false：未分配</returns>
        public bool IsAllot(string BillNo, string MaterialID)
        {
            try
            {
                var sql =
                    $@"SELECT  COUNT(1)
FROM    ngoods.ngoods_DC_VirtualStock
WHERE   SourceBillNo = '{BillNo}'
        AND MaterialID = '{MaterialID}'
        AND ExtrSource = '{UserInfo.UserExtrSource}'
        AND CompanyID = '{UserInfo.UserCompanyId}' ";

                var cmd = Helper.GetSqlStringCommand(sql);
                if (Convert.ToInt32(Helper.ExecuteScalar(cmd)) > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        //作者：曹亚亮
        //时间：2017-05-16
        /// <summary>
        /// 根据单号和货号ID获取以分配的虚拟库存数据
        /// </summary>
        /// <param name="BillNo">单号</param>
        /// <param name="MaterialID">货号ID</param>
        /// <returns></returns>
        public DataTable Get_DC_VirtualStock(string BillNo, string MaterialID)
        {
            try
            {
                var sql = $@"SELECT  DVS.CompanyID ,
        DVS.ExtrSource ,
        DVS.SourceBillNo ,
        DVS.StockID ,
        BS.StockName ,
        DVS.MaterialID ,
        DVS.SizeID ,
        SMS.SizeName ,
        DVS.TotalQty
FROM    ngoods.ngoods_DC_VirtualStock DVS ,
        nods.nods_erp_SD_Mat_Size SMS ,
        nods.nods_erp_Bas_Stock BS
WHERE   DVS.ExtrSource = '{UserInfo.UserExtrSource}'
        AND DVS.CompanyID = '{UserInfo.UserCompanyId}'
        AND DVS.StockID = BS.StockID
        AND DVS.ExtrSource = BS.ExtrSource
        AND DVS.CompanyID = BS.CompanyID
        AND DVS.SizeID = SMS.SizeID
        AND DVS.ExtrSource = SMS.ExtrSource
        AND SMS.SizeName <> '2XL'
        AND DVS.SourceBillNo = '{BillNo}'
        AND DVS.MaterialID = '{MaterialID}'
ORDER BY DVS.StockID ,
        DVS.MaterialID ,
        SMS.SizeCodeSort ";

                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion

        #region 选货号

        [MustLogin]
        [Description("获取选货号初始分配计算数据API")]
        [ReturnType(typeof(PaperData<PickingOperationsModel.SetDistributionModel>))]
        public ApiResult GetDistribution([ParamDescription("配货类型：（1 可用库存 2 成品外发到货通知单）")] int AllotType, [ParamDescription("仓库ID")] string StockID, [ParamDescription("货号ID如：NO01,NO02")] string MaterialID)
        {
            MaterialID = MaterialID.Replace("/n", "").Replace("/r", "").Trim();
            if (string.IsNullOrEmpty(StockID))
            {
                return Error("仓库ID不能为空");
            }
            if (string.IsNullOrEmpty(MaterialID))
            {
                return Error("货号ID不能为空");
            }
            if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
            {
                return Error("该用户不属于任何公司!");
            }
            if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
            {
                return Error("该用户所在公司没有任何数据源!");
            }
            if (!string.IsNullOrEmpty(MaterialID))
            {
                MaterialID = "'" + MaterialID.Replace("，", ",").TrimEnd(',').TrimStart(',').Replace(",", "','") + "'";
            }

            try
            {
                if (AllotType == 2)
                {
                    var dt = GetFictitiousNo(StockID, MaterialID);
                    var list = ModelConvertHelper<PickingOperationsModel.SetDistributionModel>.ConvertToModel(dt);
                    return Success(list);
                }
                else
                {
                    var list = GetAvailableNo(StockID, MaterialID);
                    return Success(list);
                }

            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        //作者：曹亚亮
        //时间：2017-05-19
        /// <summary>
        /// 从分配的虚拟仓获取可分配库存数量
        /// </summary>
        /// <param name="StockID">仓库ID</param>
        /// <param name="MaterialID">货号编号：如（'NO01','NO02','NO03'）</param>
        /// <returns></returns>
        public DataTable GetFictitiousNo(string StockID, string MaterialID)
        {
            try
            {
                var sql = $@"SELECT  DVS.CompanyID ,
        DVS.StockID ,
        BS.StockName ,
        DVS.MaterialID ,
        SMM.MaterialShortName ,
        DVS.SizeID ,
        SMS.SizeName ,
        SUM(ISNULL(DVS.TotalQty, 0)) - SUM(ISNULL(TCUQ.ActualQty, 0)) AS TotalQty ,
        0 AS Percentage ,
        0 AS AllottedNum ,
        0 AS ReserveNum
FROM    ngoods.ngoods_DC_VirtualStock DVS
        LEFT JOIN ( SELECT  *
                    FROM    nods.nods_erp_Bas_Stock
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                  ) BS ON DVS.StockID = BS.StockID
                          AND DVS.ExtrSource = BS.ExtrSource
                          AND DVS.CompanyID = BS.CompanyID
        LEFT JOIN ( SELECT  *
                    FROM    nods.nods_erp_SD_Mat_Size
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                  ) AS SMS ON DVS.SizeID = SMS.SizeID
                              AND SMS.SizeName <> '2XL'
                              AND DVS.ExtrSource = SMS.ExtrSource
        LEFT JOIN ( SELECT  *
                    FROM    nods.nods_erp_SD_Mat_Material2
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                  ) AS SMM ON DVS.CompanyID = SMM.CompanyID
                              AND DVS.MaterialID = SMM.MaterialID
                              AND DVS.ExtrSource = SMM.ExtrSource
        LEFT JOIN ( SELECT  *
                    FROM    ngoods.ngoods_DC_TaskCanUseQty
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                  ) AS TCUQ ON TCUQ.StockID = DVS.StockID
                               AND TCUQ.MaterialID = DVS.MaterialID
                               AND TCUQ.SizeID = DVS.SizeID
                               AND TCUQ.TaskType = 2
                               AND DVS.ExtrSource = TCUQ.ExtrSource
                               AND DVS.CompanyID = TCUQ.CompanyID
WHERE   DVS.ExtrSource = '{UserInfo.UserExtrSource}'
        AND DVS.CompanyID = '{UserInfo.UserCompanyId}'
        AND DVS.StockID = '{StockID}'
{(string.IsNullOrEmpty(MaterialID) ? "" : $@" AND DVS.MaterialID in({MaterialID}) ")}
GROUP BY DVS.CompanyID ,
        DVS.StockID ,
        BS.StockName ,
        DVS.MaterialID ,
        SMM.MaterialShortName ,
        DVS.SizeID ,
        SMS.SizeName ,
        SMS.SizeCodeSort
ORDER BY DVS.StockID ,
        DVS.MaterialID ,
        SMS.SizeCodeSort ";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        //作者：曹亚亮
        //时间：2017-05-19
        /// <summary>
        /// 从可用库存获取可分配的库存数量
        /// <param name="StockID">仓库ID</param>
        /// <param name="MaterialID">货号编号：如（'NO01','NO02','NO03'）</param>
        /// <returns></returns>
        public IEnumerable<PickingOperationsModel.SetDistributionModel> GetAvailableNo(string StockID, string MaterialID)
        {
            try
            {
                var hk = new HKERPEntities();
                //按仓库计算库存
                var list = new List<PickingOperationsModel.GetAvailableNoModel>();

                switch (UserInfo.UserExtrSource)
                {
                    case "KM":
                        list = KM_AvailableNo(hk, StockID, MaterialID).ToList();
                        break;
                    default:
                        break;
                }
                var sizeList = new SizeTypeHandler().GetOdsErpSDMatSizeList();
                var data = list.Select(t => new PickingOperationsModel.SetDistributionModel
                {
                    CompanyID = UserInfo.UserCompanyId,
                    StockID = t.StockID,
                    StockName = t.StockName,
                    MaterialID = t.MaterialID,
                    MaterialShortName = t.MaterialShortName,
                    SizeID = sizeList.FirstOrDefault(d => d.SizeName == t.SizeName)?.SizeID,
                    SizeCodeSort = sizeList.FirstOrDefault(d => d.SizeName == t.SizeName)?.SizeCodeSort,
                    SizeName = t.SizeName,
                    TotalQty = t.CanQty,
                    Percentage = 0,
                    AllottedNum = 0,
                    ReserveNum = 0,
                    ExtrSource = UserInfo.UserExtrSource,
                });
                foreach (var item in data)
                {
                    if (item.SizeCodeSort.Length == 1)
                    {
                        item.SizeCodeSort = item.SizeCodeSort.PadLeft(2, '0');
                    }
                }
                var temp = data.OrderBy(g => g.StockID).ThenBy(g => g.MaterialID).ThenBy(g => g.SizeCodeSort);
                return temp;

            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        //作者：ljj
        //时间：2017-07-26
        public IList<PickingOperationsModel.GetAvailableNoModel> KM_AvailableNo(HKERPEntities hk, string StockID, string MaterialID)
        {
            try
            {
                var calSql =
                    $@"declare @Retval int Exec dbo.[spSD_InvReCalcInvWasteBook] @Retval output, '{UserInfo.UserCompanyId}', ' And StockID in (''{StockID}'')'";
                hk.Database.SqlQuery<object>(calSql);

                //按仓库、货号获取库存
                MaterialID = MaterialID.Replace("','", "'',''");
                var getSql =
                    $@"exec spSD_Rpt_InvAnalysis_MovAndSal '{UserInfo.UserCompanyId}','','{StockID}',' And MaterialID IN ('{MaterialID}')',2,'',1,0,''";

                return hk.Database.SqlQuery<PickingOperationsModel.GetAvailableNoModel>(getSql).ToList();
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        ////作者：曹亚亮
        ////时间：2017-06-06
        ///// <summary>
        ///// 根据尺码名称获取尺码ID
        ///// </summary>
        ///// <param name="SizeName">尺码名称</param>
        ///// <returns>返回空字符为：未找到对应尺码</returns>
        //public string GetSizeID(string SizeName)
        //{
        //    try
        //    {
        //        string strSizeID = string.Empty;
        //        var sql = string.Format(@"SELECT SizeID FROM ods.ods_erp_SD_Mat_Size WHERE SizeName='{0}' ", SizeName);

        //        var cmd = Helper.GetSqlStringCommand(sql);
        //        var SizeID = Helper.ExecuteScalar(cmd);

        //        if (SizeID != null)
        //        {
        //            strSizeID = Convert.ToString(SizeID);
        //            return strSizeID;
        //        }
        //        else
        //        {
        //            return strSizeID;
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        throw (ex);
        //    }
        //}

        ////作者：曹亚亮
        ////时间：2017-06-15
        ///// <summary>
        ///// 根据尺码名称获取尺码的排序值
        ///// </summary>
        ///// <param name="SizeName">尺码名称</param>
        ///// <returns>0：未找到对应尺码</returns>
        //public int GetSizeCodeSort(string SizeName)
        //{
        //    try
        //    {
        //        var intSizeCodeSort = 0;
        //        var sql = $@"SELECT SizeCodeSort FROM ods.ods_erp_SD_Mat_Size WHERE SizeName='{SizeName}' ";

        //        var cmd = Helper.GetSqlStringCommand(sql);
        //        var SizeID = Helper.ExecuteScalar(cmd);

        //        if (SizeID != null)
        //        {
        //            intSizeCodeSort = Convert.ToInt32(SizeID);
        //            return intSizeCodeSort;
        //        }
        //        else
        //        {
        //            return intSizeCodeSort;
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        throw (ex);
        //    }
        //}

        #endregion

        #region 店铺配货需求

        [MustLogin]
        [Description("根据货号获取店铺配货需求量数据列表API")]
        [ReturnType(typeof(PaperData<PickingOperationsModel.GetShopDistributionModel>))]
        public ApiResult GetShopDistributionList([ParamDescription("货号ID")] string MaterialID, [ParamDescription("仓库ID")] string StockID)
        {
            if (string.IsNullOrEmpty(MaterialID))
            {
                return Error("货号ID不能为空");
            }
            if (string.IsNullOrEmpty(StockID))
            {
                return Error("仓库ID不能为空");
            }
            if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
            {
                return Error("该用户不属于任何公司!");
            }
            if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
            {
                return Error("该用户所在公司没有任何数据源!");
            }
            try
            {
                var dt = GetShopDistribution(MaterialID, StockID);
                var list = ModelConvertHelper<PickingOperationsModel.GetShopDistributionModel>.ConvertToModel(dt);
                return Success(list);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        //作者：曹亚亮
        //时间：2017-05-19
        /// <summary>
        /// 根据货号和仓库ID获取店铺配货需求量数据列表
        /// </summary>
        /// <param name="MaterialID">货号编号</param>
        /// <param name="StockID">仓库ID</param>
        /// <returns></returns>
        public DataTable GetShopDistribution(string MaterialID, string StockID)
        {
            try
            {
                var sql = $@"SELECT  DDS.CompanyID ,
        DDS.ShopID ,
        DDS.ShopCode ,
        DDS.ShopName ,
        DDS.StockID ,
        mt.MaterialID ,
        mt.ModelID ,
        mt.ItemID ,
        mts.SizeID ,
        SMS.SizeName ,
        EBAP.AreaName AS ProvinceName ,
        EBA.AreaName AS CityName ,
        CASE WHEN ISNULL(odp.Qty, 0) > 0 THEN 'Y'
             ELSE ''
        END AS OrderFlag ,
        CASE WHEN ISNULL(DBP.Qty, 0) > 0
                  AND ISNULL(mq.FirstDCQty, 0) > 0
             THEN ( CASE WHEN ISNULL(DBP.Qty, 0) > ISNULL(mq.FirstDCQty, 0)
                         THEN ISNULL(mq.FirstDCQty, 0)
                         ELSE ISNULL(DBP.Qty, 0)
                    END )
             ELSE ( CASE WHEN ISNULL(DBP.Qty, 0) > ISNULL(mq.FirstDCQty, 0)
                         THEN ISNULL(DBP.Qty, 0)
                         ELSE ISNULL(mq.FirstDCQty, 0)
                    END )
        END AS Qty ,
        CASE WHEN ISNULL(osq.CompanyID, '') <> '' THEN 'Y'
             ELSE ''
        END AS MoveFlag
FROM    nods.nods_erp_SD_Mat_Material AS mt
        INNER JOIN ( SELECT *
                     FROM   nods.nods_erp_SD_Mat_MaterialSize
                     WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                   ) AS mts ON mt.MaterialID = mts.MaterialID
                               AND mt.ExtrSource = mts.ExtrSource
        INNER JOIN ( SELECT *
                     FROM   nods.nods_erp_SD_Mat_Size
                     WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                   ) AS SMS ON mts.SizeID = SMS.SizeID
                               AND mt.ExtrSource = SMS.ExtrSource
        INNER JOIN ( SELECT *
                     FROM   nods.nods_erp_Bas_Stock
                     WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                   ) AS EBS ON EBS.AllowUsed = 1
                               AND mt.ExtrSource = EBS.ExtrSource
        INNER JOIN ( SELECT *
                     FROM   ndw.ndw_AreaProCity
                     WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                   ) AS APC ON EBS.StockID = APC.StockID
                               AND mt.ExtrSource = APC.ExtrSource
                               AND EBS.CompanyID = APC.CompanyID
        INNER JOIN ( SELECT *
                     FROM   ndw.ndw_Shop
                     WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                   ) AS DDS ON DDS.AreaID = APC.AreaID
                               AND ( DDS.ShopState = '正常营业'
                                     OR DDS.ShopState = '待开业'
                                   )
                               AND DDS.ISDwMvShop = 1
                               AND mt.ExtrSource = DDS.ExtrSource
                               AND EBS.CompanyID = DDS.CompanyID
        LEFT JOIN ( SELECT  ExtrSource ,
                            CompanyID ,
                            ShopID ,
                            ShopCode ,
                            MaterialID ,
                            TotalQty ,
                            SizeID ,
                            SizeLength ,
                            SizeMinQty ,
                            Qty
                    FROM    ngoods.ngoods_DC_Bas_PLOrder
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                    GROUP BY ExtrSource ,
                            CompanyID ,
                            ShopID ,
                            ShopCode ,
                            MaterialID ,
                            TotalQty ,
                            SizeID ,
                            SizeLength ,
                            SizeMinQty ,
                            Qty
                  ) AS DBP ON mt.MaterialID = DBP.MaterialID
                              AND mt.ExtrSource = DBP.ExtrSource
                              AND EBS.CompanyID = DBP.CompanyID
                              AND DDS.ShopID = DBP.ShopID
                              AND mts.SizeID = DBP.SizeID
        LEFT JOIN ( SELECT  *
                    FROM    ngoods.ngoods_DC_Bas_ModelMinQty
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                  ) AS mq ON mt.ExtrSource = mq.ExtrSource
                             AND EBS.CompanyID = mq.CompanyID
                             AND DDS.ShopID = mq.ShopID
                             AND mt.ModelID = mq.ModelID
                             AND mt.ItemID = mq.ItemID
                             AND mts.SizeID = mq.SizeID
                             AND DBP.SizeLength = mq.SizeLength
        LEFT JOIN ( SELECT  CompanyID ,
                            ExtrSource ,
                            ShopID ,
                            MaterialID ,
                            SUM(Qty) Qty
                    FROM    ngoods.ngoods_DC_Bas_PLOrder
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                    GROUP BY CompanyID ,
                            ExtrSource ,
                            ShopID ,
                            MaterialID
                  ) AS odp ON mt.ExtrSource = odp.ExtrSource
                              AND EBS.CompanyID = odp.CompanyID
                              AND DDS.ShopID = odp.ShopID
                              AND mt.MaterialID = odp.MaterialID
        LEFT JOIN ( SELECT  a.CompanyID ,
                            a.ExtrSource ,
                            b.ShopID ,
                            b.MaterialID
                    FROM    ngoods.ngoods_DC_TaskMaster a
                            INNER JOIN ( SELECT *
                                         FROM   ngoods.ngoods_DC_ShopReqDetail
                                         WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                                AND CompanyID = '{UserInfo.UserCompanyId}'
                                       ) b ON a.CompanyID = b.CompanyID
                                              AND a.ExtrSource = b.ExtrSource
                                              AND a.TaskBillNo = b.TaskBillNo
                    WHERE   a.ExtrSource = '{UserInfo.UserExtrSource}'
                            AND a.CompanyID = '{UserInfo.UserCompanyId}'
                            AND ( TaskFlag = 3
                                  OR TaskFlag = 4
                                )
                            AND b.ActualQty > 0
                    GROUP BY a.CompanyID ,
                            a.ExtrSource ,
                            b.ShopID ,
                            b.MaterialID
                  ) AS osq ON DDS.ShopID = osq.ShopID
                              AND mt.MaterialID = osq.MaterialID
                              AND mt.ExtrSource = osq.ExtrSource
                              AND EBS.CompanyID = osq.CompanyID
        LEFT JOIN ( SELECT  *
                    FROM    nods.nods_erp_Bas_Area
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                  ) AS EBA ON APC.AreaID = EBA.AreaID
                              AND mt.ExtrSource = eba.ExtrSource
        INNER JOIN ( SELECT *
                     FROM   nods.nods_erp_Bas_Area
                     WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                   ) AS EBAP ON EBA.ParentID = EBAP.AreaID
                                AND EBA.AllowUsed = 1
                                AND EBA.Level = 2
                                AND mt.ExtrSource = EBAP.ExtrSource
WHERE   mt.ExtrSource = '{UserInfo.UserExtrSource}'
        AND EBS.CompanyID = '{UserInfo.UserCompanyId}'
        AND mt.MaterialID = '{MaterialID}'
        AND ( EBS.StockID = '{StockID}'
              OR EBS.StockID IN ( SELECT    PhysicsStockID
                                  FROM      ngoods.ngoods_DC_VirtuaForPhysics
                                  WHERE     VirtualStockID = '{StockID}'
                                            AND ExtrSource = '{UserInfo.UserExtrSource}'
                                            AND CompanyID = '{UserInfo.UserCompanyId}' )
              OR EBS.StockID IN (
              SELECT    PhysicsStockID
              FROM      ngoods.ngoods_DC_VirtuaForPhysics
              WHERE     ExtrSource = '{UserInfo.UserExtrSource}'
                        AND CompanyID = '{UserInfo.UserCompanyId}'
                        AND VirtualStockID IN (
                        SELECT  PhysicsStockID
                        FROM    ngoods.ngoods_DC_VirtuaForPhysics
                        WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                                AND CompanyID = '{UserInfo.UserCompanyId}'
                                AND VirtualStockID = '{StockID}' ) )
            )
ORDER BY ( CASE WHEN ISNULL(odp.Qty, 0) > 0 THEN 'Y'
                ELSE ''
           END ) DESC ,
        ( CASE WHEN ISNULL(osq.CompanyID, '') <> '' THEN 'Y'
               ELSE ''
          END ) ,
        DDS.ShopName ,
        SMS.SizeCodeSort ";

                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion

        #region 计算店铺排序

        [MustLogin]
        [JsonType]
        [Description("保存配货任务单号和配货可用库存数和计算店铺排序API")]
        [ReturnType(typeof(int), "0－成功")]
        public ApiResult UpdateShopSort(PickingOperationsModel.SetShopSortModel param)
        {
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                if (param == null) return Error("参数对象不能为空");
                if (string.IsNullOrEmpty(param.TaskBillNo)) return Error("任务单号不能为空");
                if (string.IsNullOrEmpty(param.TaskType)) return Error("配货方式不能为空");
                if (string.IsNullOrEmpty(param.CompanyID)) return Error("公司代码不能为空");
                if (string.IsNullOrEmpty(param.OutStockID)) return Error("配货仓库内码不能为空");
                if (param.StockList == null || param.StockList.Count == 0) return Error("库存分配比例设置不能为空");
                if (param.ShopList == null || param.ShopList.Count == 0) return Error("店铺配货需求量不能为空");

                var isNum = Update_DC_ShopReqDetail(param);
                switch (isNum)
                {
                    case 1:
                        return Error("删除对应任务单数据报错");
                    case 2:
                        return Error("添加对应任务单数据报错");
                    case 3:
                        return Error("更新店铺排序失败");
                    case 4:
                        return Error("任务单号已存在请重新获取任务单号");
                    default:
                        return Success(0);
                }
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        //作者：曹亚亮
        //时间：2017-06-07
        /// <summary>
        /// 保存配货任务单号和配货可用库存数和计算店铺排序
        /// </summary>
        /// <param name="param">计算店铺排序参数实体类</param>
        /// <returns></returns>
        public int Update_DC_ShopReqDetail(PickingOperationsModel.SetShopSortModel param)
        {
            try
            {
                var delNum = 1;
                if (!string.IsNullOrEmpty(IsTaskBillNo2(param.TaskBillNo)))
                {
                    return 4; //任务单号以存在请求重新生成
                    //delNum = DeleteTaskBillNoData(param.TaskBillNo); //放弃删除
                }
                if (delNum > 0)
                {
                    var insertNum = InsertTaskBillNoData(param);
                    if (insertNum > 0)
                    {
                        var updateNum = UpdateDC_ShopSort(param.TaskBillNo);
                        return updateNum > 0 ? 0 : 3;
                    }
                    else
                    {
                        return 2; //添加对应任务单数据报错
                    }
                }
                else
                {
                    return 1; //删除对应任务单数据报错
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        //作者：曹亚亮
        //时间：2017-06-08
        /// <summary>
        /// 判断任务单号是否以存在数据表中
        /// </summary>
        /// <param name="TaskBillNo">单号前缀</param>
        /// <returns>返回空字符为：未创建 不为空以存在</returns>
        public string IsTaskBillNo2(string TaskBillNo)
        {
            try
            {
                var strOddNum = string.Empty;
                var sql =
                    $@"SELECT  TaskBillNo
FROM    ngoods.ngoods_DC_TaskMaster
WHERE   TaskBillNo = '{TaskBillNo}'
        AND ExtrSource = '{UserInfo.UserExtrSource}'
        AND CompanyID = '{UserInfo.UserCompanyId}' 
limit 1";

                var cmd = Helper.GetSqlStringCommand(sql);
                var oddNum = Helper.ExecuteScalar(cmd);

                if (oddNum != null)
                {
                    strOddNum = Convert.ToString(oddNum);
                    return strOddNum;
                }
                else
                {
                    return strOddNum;
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        //作者：曹亚亮
        //时间：2017-6-06-09
        /// <summary>
        /// 根据任务单号删除 配货任务单主表、配货可用库存数表、店铺需求表 中的数据
        /// </summary>
        /// <param name="TaskBillNo">任务单号</param>
        /// <returns></returns>
        public int DeleteTaskBillNoData(string TaskBillNo)
        {
            try
            {
                var sb = new StringBuilder();
                var delTaskMasterSql =
                    $@"DELETE  FROM ngoods.ngoods_DC_TaskMaster
WHERE   CompanyID = '{UserInfo.UserCompanyId}'
        AND ExtrSource = '{UserInfo.UserExtrSource}'
        AND TaskBillNo = '{TaskBillNo}';"; //配货任务单主表
                var delTaskCanUseQtySql =
                    $@"DELETE  FROM ngoods.ngoods_DC_TaskCanUseQty
WHERE   CompanyID = '{UserInfo.UserCompanyId}'
        AND ExtrSource = '{UserInfo.UserExtrSource}'
        AND TaskBillNo = '{TaskBillNo}';"; //配货可用库存数
                var delShopReqDetailSql =
                    $@"DELETE  FROM ngoods.ngoods_DC_ShopReqDetail
WHERE   CompanyID = '{UserInfo.UserCompanyId}'
        AND ExtrSource = '{UserInfo.UserExtrSource}'
        AND TaskBillNo = '{TaskBillNo}';"; //店铺需求表
                sb.AppendLine(delTaskMasterSql);
                sb.AppendLine(delTaskCanUseQtySql);
                sb.AppendLine(delShopReqDetailSql);
                var cmd = Helper.GetSqlStringCommand(sb.ToString());
                return Helper.ExecuteNonQuery(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        //作者：曹亚亮
        //时间：2017-06-09
        /// <summary>
        /// 保存任务单号数据到 配货任务单主表、配货可用库存数表、店铺需求表 中
        /// </summary>
        /// <param name="param">数据实体类</param>
        /// <returns></returns>
        public int InsertTaskBillNoData(PickingOperationsModel.SetShopSortModel param)
        {
            var returnNum = 0;
            try
            {
                //保存配货任务单
                var insertTaskMasterSql = $@"INSERT  INTO ngoods.ngoods_DC_TaskMaster
        ( ExtrSource ,
          CompanyID ,
          TaskBillNo ,
          TaskType ,
          Operator ,
          OperateDate ,
          TaskFlag ,
          StockID
        )
VALUES  ( '{UserInfo.UserExtrSource}' ,
          '{UserInfo.UserCompanyId}' ,
          '{param.TaskBillNo}' ,
          '{param.TaskType}' ,
          '{UserInfo.UserID}' ,
          '{DateTime.Now}' ,
          1 ,
          '{param.OutStockID}'
        );";
                var cmd = Helper.GetSqlStringCommand(insertTaskMasterSql);
                var count = Helper.ExecuteNonQuery(cmd);

                if (count > 0)
                {
                    //保存配货可用库存数
                    SaveTaskCanUseQty(param);
                    if (IsDC_TaskCanUseQty(param.TaskBillNo))
                    {
                        //保存店铺需求
                        returnNum = SaveShopReqDetail(param.ShopList, param.TaskBillNo, param.OutStockID);
                        if (returnNum <= 0)
                        {
                            DeleteTaskBillNoData(param.TaskBillNo);
                        }
                    }
                    else
                    {
                        DeleteTaskBillNoData(param.TaskBillNo);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            return returnNum;
        }

        //作者：曹亚亮
        //时间：2017-07-25
        /// <summary>
        /// 保存配货可用库存数
        /// </summary>
        /// <param name="param">请求参数实体类</param>
        public void SaveTaskCanUseQty(PickingOperationsModel.SetShopSortModel param)
        {
            try
            {
                //INSERT INTO goods.goods_DC_TaskCanUseQty(CompanyID,TaskBillNo,TaskType,StockID,MaterialID,SizeID,TotalQty,MoveRate,CanUseQty,Operator,OperateDate) 
                var dtInsert = DataTableHelper.LinqToDataTable(param.StockList.Select(
                    p => new
                    {
                        CompanyID = UserInfo.UserCompanyId,
                        ExtrSource = UserInfo.UserExtrSource,
                        param.TaskBillNo,
                        param.TaskType,
                        StockID = param.OutStockID,
                        p.MaterialID,
                        p.SizeID,
                        p.TotalQty,
                        MoveRate = p.Percentage,
                        CanUseQty = p.AllottedNum,
                        Operator = UserInfo.UserID,
                        OperateDate = DateTime.Now
                    }).ToList());
                Helper.BulkCopy(dtInsert, "ngoods.ngoods_DC_TaskCanUseQty");
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        //作者：曹亚亮
        //时间：2017-07-25
        /// <summary>
        /// 判断保存配货可用库存数是否成功
        /// </summary>
        /// <param name="TaskBillNo">任务单号</param>
        /// <returns></returns>
        public bool IsDC_TaskCanUseQty(string TaskBillNo)
        {
            try
            {
                var sql = $@"SELECT  COUNT(1)
FROM    ngoods.ngoods_DC_TaskCanUseQty
WHERE   CompanyID = '{UserInfo.UserCompanyId}'
        AND ExtrSource = '{UserInfo.UserExtrSource}'
        AND TaskBillNo = '{TaskBillNo}'";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Convert.ToInt32(Helper.ExecuteScalar(cmd)) > 0;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        //作者：ljj
        //时间：2017-07-28
        public void CreateTempTable(DataTable dt, string TempTableName)
        {
            try
            {
                var sql = $@"CREATE TABLE {TempTableName}
    (
      CompanyID VARCHAR(30) ,
      ExtrSource VARCHAR(30) ,
      MaterialID VARCHAR(30) ,
      ShopID VARCHAR(20)
    )";
                Helper.ExecuteSqlTran(dt, TempTableName, sql);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        //作者：ljj
        //时间：2017-07-28
        public void DropTempTable(string TempTableName)
        {
            try
            {
                Helper.DropTempTable(TempTableName);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }


        //作者：曹亚亮
        //时间：2017-07-25
        /// <summary>
        /// 保存店铺需求
        /// </summary>
        /// <param name="ShopList">货号对应店铺集合</param>
        /// <param name="TaskBillNo">任务单号</param>
        /// <param name="OutStockID">发货仓ID</param>
        /// <returns></returns>
        public int SaveShopReqDetail(List<PickingOperationsModel.GetShopDistributionModel> ShopList, string TaskBillNo, string OutStockID)
        {
            var insertNum = 0;
            var uid = Guid.NewGuid().ToString().Replace("-", "");
            var tempTableName = $@"ngoods.temp{uid}";
            try
            {
                var dtInsert = DataTableHelper.LinqToDataTable(ShopList.Select(
                    p => new
                    {
                        CompanyID = UserInfo.UserCompanyId,
                        ExtrSource = UserInfo.UserExtrSource,
                        p.MaterialID,
                        p.ShopID
                    }).ToList());
                DropTempTable(tempTableName);
                CreateTempTable(dtInsert, tempTableName);

                var insertShopReqDetailSql =
                    $@"INSERT  INTO ngoods.ngoods_DC_ShopReqDetail
        ( CompanyID ,
          ExtrSource ,
          TaskBillNo ,
          OutStockID ,
          ShopID ,
          StockID ,
          MaterialID ,
          SizeID ,
          Qty ,
          Operator ,
          OperateDate
        )
SELECT  mt.ExtrSource ,
        DDS.CompanyID ,
        '{TaskBillNo}' AS TaskBillNo ,
        '{OutStockID}' AS OutStockID ,
        DDS.ShopID ,
        DDS.StockID ,
        mt.MaterialID ,
        mts.SizeID ,
        CASE WHEN ISNULL(DBP.Qty, 0) > 0
                  AND ISNULL(mq.FirstDCQty, 0) > 0
             THEN ( CASE WHEN ISNULL(DBP.Qty, 0) > ISNULL(mq.FirstDCQty, 0)
                         THEN ISNULL(mq.FirstDCQty, 0)
                         ELSE ISNULL(DBP.Qty, 0)
                    END )
             ELSE ( CASE WHEN ISNULL(DBP.Qty, 0) > ISNULL(mq.FirstDCQty, 0)
                         THEN ISNULL(DBP.Qty, 0)
                         ELSE ISNULL(mq.FirstDCQty, 0)
                    END )
        END AS Qty ,
        '{UserInfo.UserID}' AS Operator ,
        GETDATE() AS OperateDate
FROM    nods.nods_erp_SD_Mat_Material AS mt
        INNER JOIN ( SELECT *
                     FROM   nods.nods_erp_SD_Mat_MaterialSize
                     WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                   ) AS mts ON mt.MaterialID = mts.MaterialID
                               AND mt.ExtrSource = mts.ExtrSource
        INNER JOIN ( SELECT *
                     FROM   nods.nods_erp_SD_Mat_Size
                     WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                   ) AS SMS ON mts.SizeID = SMS.SizeID
                               AND mt.ExtrSource = SMS.ExtrSource
        INNER JOIN ( SELECT *
                     FROM   nods.nods_erp_Bas_Stock
                     WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                   ) AS EBS ON EBS.AllowUsed = 1
                               AND mt.ExtrSource = EBS.ExtrSource
        INNER JOIN ( SELECT *
                     FROM   ndw.ndw_AreaProCity
                     WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                   ) AS APC ON EBS.StockID = APC.StockID
                               AND mt.ExtrSource = APC.ExtrSource
                               AND EBS.CompanyID = APC.CompanyID
        INNER JOIN ( SELECT *
                     FROM   ndw.ndw_Shop
                     WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                   ) AS DDS ON DDS.AreaID = APC.AreaID
                               AND ( DDS.ShopState = '正常营业'
                                     OR DDS.ShopState = '待开业'
                                   )
                               AND mt.ExtrSource = DDS.ExtrSource
                               AND EBS.CompanyID = DDS.CompanyID
        INNER JOIN ( SELECT ExtrSource ,
                            CompanyID ,
                            MaterialID ,
                            ShopID
                     FROM   {tempTableName}
                     WHERE  CompanyID = '{UserInfo.UserCompanyId}'
                            AND ExtrSource = '{UserInfo.UserExtrSource}'
                     GROUP BY ExtrSource ,
                            CompanyID ,
                            MaterialID ,
                            ShopID
                   ) msp ON mt.MaterialID = msp.MaterialID
                            AND DDS.ShopID = msp.ShopID
                            AND mt.ExtrSource = msp.ExtrSource
                            AND EBS.CompanyID = msp.CompanyID
        LEFT JOIN ( SELECT  *
                    FROM    ngoods.ngoods_DC_Bas_PLOrder
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                  ) AS DBP ON mt.MaterialID = DBP.MaterialID
                              AND DDS.ShopID = DBP.ShopID
                              AND mts.SizeID = DBP.SizeID
                              AND mt.ExtrSource = DBP.ExtrSource
                              AND EBS.CompanyID = DBP.CompanyID
        LEFT JOIN ( SELECT  *
                    FROM    ngoods.ngoods_DC_Bas_ModelMinQty
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                  ) AS mq ON EBS.CompanyID = mq.CompanyID
                             AND DDS.ShopID = mq.ShopID
                             AND mt.ModelID = mq.ModelID
                             AND mt.ItemID = mq.ItemID
                             AND mts.SizeID = mq.SizeID
                             AND DBP.SizeLength = mq.SizeLength
                             AND mt.ExtrSource = mq.ExtrSource
        LEFT JOIN ( SELECT  CompanyID ,
                            ExtrSource ,
                            ShopID ,
                            MaterialID ,
                            SUM(Qty) Qty
                    FROM    ngoods.ngoods_DC_Bas_PLOrder
                    WHERE   CompanyID = '{UserInfo.UserCompanyId}'
                            AND ExtrSource = '{UserInfo.UserExtrSource}'
                    GROUP BY CompanyID ,
                            ExtrSource ,
                            ShopID ,
                            MaterialID
                  ) AS odp ON DDS.ShopID = odp.ShopID
                              AND mt.MaterialID = odp.MaterialID
                              AND mt.ExtrSource = odp.ExtrSource
                              AND EBS.CompanyID = odp.CompanyID
        LEFT JOIN ( SELECT  a.CompanyID ,
                            a.ExtrSource ,
                            b.ShopID ,
                            b.MaterialID
                    FROM    ngoods.ngoods_DC_TaskMaster a
                            INNER JOIN ( SELECT *
                                         FROM   ngoods.ngoods_DC_ShopReqDetail
                                         WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                                AND CompanyID = '{UserInfo.UserCompanyId}'
                                       ) b ON a.CompanyID = b.CompanyID
                                              AND a.TaskBillNo = b.TaskBillNo
                                              AND a.ExtrSource = b.ExtrSource
                    WHERE   ( TaskFlag = 3
                              OR TaskFlag = 4
                            )
                            AND b.ActualQty > 0
                            AND a.CompanyID = '{UserInfo.UserCompanyId}'
                            AND a.ExtrSource = '{UserInfo.UserExtrSource}'
                    GROUP BY a.CompanyID ,
                            a.ExtrSource ,
                            b.ShopID ,
                            b.MaterialID
                  ) AS osq ON DDS.ShopID = osq.ShopID
                              AND mt.MaterialID = osq.MaterialID
                              AND mt.ExtrSource = osq.ExtrSource
                              AND EBS.CompanyID = osq.CompanyID
        LEFT JOIN ( SELECT  *
                    FROM    nods.nods_erp_Bas_Area
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                  ) AS EBA ON APC.AreaID = EBA.AreaID
                              AND mt.ExtrSource = EBA.ExtrSource
        INNER JOIN ( SELECT *
                     FROM   nods.nods_erp_Bas_Area
                     WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                   ) AS EBAP ON EBA.ParentID = EBAP.AreaID
                                AND EBA.AllowUsed = 1
                                AND EBA.Level = 2
                                AND mt.ExtrSource = EBAP.ExtrSource
WHERE   mt.ExtrSource = '{UserInfo.UserExtrSource}'
        AND EBS.CompanyID = '{UserInfo.UserCompanyId}'
        AND ( EBS.StockID = '{OutStockID}'
              OR EBS.StockID IN ( SELECT    PhysicsStockID
                                  FROM      ngoods.ngoods_DC_VirtuaForPhysics
                                  WHERE     VirtualStockID = '{OutStockID}'
                                            AND ExtrSource = '{UserInfo.UserExtrSource}'
                                            AND CompanyID = '{UserInfo.UserCompanyId}' )
              OR EBS.StockID IN (
              SELECT    PhysicsStockID
              FROM      ngoods.ngoods_DC_VirtuaForPhysics
              WHERE     ExtrSource = '{UserInfo.UserExtrSource}'
                        AND CompanyID = '{UserInfo.UserCompanyId}'
                        AND VirtualStockID IN (
                        SELECT  PhysicsStockID
                        FROM    ngoods.ngoods_DC_VirtuaForPhysics
                        WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                                AND CompanyID = '{UserInfo.UserCompanyId}'
                                AND VirtualStockID = '{OutStockID}' ) )
            )
ORDER BY ( CASE WHEN ISNULL(odp.Qty, 0) > 0 THEN 'Y'
                ELSE ''
           END ) DESC ,
        ( CASE WHEN ISNULL(osq.CompanyID, '') <> '' THEN 'Y'
               ELSE ''
          END ) ,
        DDS.ShopName ,
        SMS.SizeCodeSort";
                var cmd = Helper.GetSqlStringCommand(insertShopReqDetailSql);
                insertNum = Helper.ExecuteNonQuery(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                DropTempTable(tempTableName);
            }
            return insertNum;
        }

        //作者：曹亚亮
        //时间：2017-06-09
        /// <summary>
        /// 根据任务单号修改店铺的排序
        /// </summary>
        /// <param name="TaskBillNo">任务单号</param>
        /// <returns></returns>
        public int UpdateDC_ShopSort(string TaskBillNo)
        {
            try
            {
                //获取任务单号对应的每个货号的更新率
                var dt = GetUpdateRate(TaskBillNo);
                var list = ModelConvertHelper<PickingOperationsModel.GetUpdateRateModel>.ConvertToModel(dt);
                var sb = new StringBuilder();
                foreach (var t in list)
                {
                    if (t.UpdateRate < Convert.ToDecimal(0.9))
                    {
                        sb.AppendLine($@"UPDATE
	ngoods.ngoods_DC_ShopReqDetail SRD
SET
	ShopNum = NUM.RowNum
FROM
	(
SELECT  a.ExtrSource ,
        a.CompanyID ,
        a.TaskBillNo ,
        a.ShopID ,
        a.MaterialID ,
        ROW_NUMBER() OVER ( PARTITION BY a.TaskBillNo, a.MaterialID ORDER BY Qty DESC, PayAmount DESC ) AS RowNum
FROM    ( SELECT
					DISTINCT
                    a.ExtrSource ,
                    a.CompanyID ,
                    a.TaskBillNo ,
                    a.ShopID ,
                    a.MaterialID ,
                    ISNULL(c.Qty, 0) AS Qty ,
                    b.ItemID ,
                    ISNULL(d.McRate, 0) AS McRate ,
                    ISNULL(e.PayAmount, 0) AS PayAmount
          FROM      ngoods.ngoods_DC_ShopReqDetail a
                    INNER JOIN ( SELECT *
                                 FROM   nods.nods_erp_SD_Mat_Material
                                 WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                               ) b ON a.MaterialID = b.MaterialID
                                      AND a.ExtrSource = b.ExtrSource
                    LEFT JOIN ( SELECT  ShopID ,
                                        MaterialID ,
                                        ExtrSource ,
                                        CompanyID ,
                                        SUM(Qty) Qty
                                FROM    ngoods.ngoods_DC_Bas_PLOrder
                                WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                                        AND CompanyID = '{UserInfo.UserCompanyId}'
                                GROUP BY ShopID ,
                                        MaterialID ,
                                        ExtrSource ,
                                        CompanyID
                              ) c ON a.MaterialID = c.MaterialID
                                     AND a.ShopID = c.ShopID
                                     AND a.ExtrSource = c.ExtrSource
                                     AND a.CompanyID = c.CompanyID
                    LEFT JOIN ( SELECT  *
                                FROM    ngoods.ngoods_DC_SatisfyRateTable
                                WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                              ) d ON a.ShopID = d.ShopID
                                     AND a.ExtrSource = d.ExtrSource
                    LEFT JOIN ( SELECT  *
                                FROM    ngoods.ngoods_DC_ItemPayTable
                                WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                              ) e ON a.ShopID = e.ShopID
                                     AND b.ItemID = e.ItemID
                                     AND a.ExtrSource = e.ExtrSource
          WHERE     a.TaskBillNo = '{t.TaskBillNo}'
                    AND a.MaterialID = '{t.MaterialID}'
                    AND a.ExtrSource = '{UserInfo.UserExtrSource}'
                    AND a.CompanyID = '{UserInfo.UserCompanyId}'
        ) A		
	) as NUM
WHERE
    SRD.ExtrSource = '{UserInfo.UserExtrSource}'
	AND SRD.CompanyID = '{UserInfo.UserCompanyId}'
	AND SRD.TaskBillNo = NUM.TaskBillNo
	AND SRD.MaterialID = NUM.MaterialID
	AND SRD.ShopID = NUM.ShopID
	AND SRD.ExtrSource = NUM.ExtrSource
	AND SRD.CompanyID = NUM.CompanyID;");
                    }
                    else
                    {
                        sb.AppendLine($@"UPDATE ngoods.ngoods_DC_ShopReqDetail SRD SET ShopNum=NUM.RowNum FROM 
(
	SELECT  TaskBillNo ,
        a.ExtrSource ,
        a.CompanyID ,
        a.ShopID ,
        a.MaterialID ,
        ROW_NUMBER() OVER ( PARTITION BY a.TaskBillNo, a.MaterialID ORDER BY McFlag, PayFlag ) AS RowNum
FROM    ( SELECT    a.ExtrSource ,
                    a.CompanyID ,
                    a.TaskBillNo ,
                    a.ShopID ,
                    a.MaterialID ,
                    McRate ,
                    PayAmount ,
                    CASE WHEN McRate <= 0.9 THEN 1
                         ELSE 2
                    END AS McFlag ,
                    CASE WHEN McRate <= 0.9
                         THEN ROW_NUMBER() OVER ( PARTITION BY a.TaskBillNo,
                                                  a.MaterialID,
                                                  CASE WHEN McRate <= 0.9
                                                       THEN 1
                                                       ELSE 2
                                                  END ORDER BY McRate, PayAmount DESC )
                         ELSE ROW_NUMBER() OVER ( PARTITION BY a.TaskBillNo,
                                                  a.MaterialID,
                                                  CASE WHEN McRate <= 0.9
                                                       THEN 1
                                                       ELSE 2
                                                  END ORDER BY PayAmount DESC )
                    END AS PayFlag
          FROM      ( SELECT DISTINCT
                                a.ExtrSource ,
                                a.CompanyID ,
                                a.TaskBillNo ,
                                a.ShopID ,
                                a.MaterialID ,
                                ISNULL(c.Qty, 0) AS Qty ,
                                b.ItemID ,
                                ISNULL(d.McRate, 0) AS McRate ,
                                ISNULL(e.PayAmount, 0) AS PayAmount
                      FROM      ngoods.ngoods_DC_ShopReqDetail a
                                INNER JOIN ( SELECT *
                                             FROM   nods.nods_erp_SD_Mat_Material
                                             WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                           ) b ON a.MaterialID = b.MaterialID
                                                  AND a.ExtrSource = b.ExtrSource
                                LEFT JOIN ( SELECT
							DISTINCT                ShopID ,
                                                    MaterialID ,
                                                    ExtrSource ,
                                                    CompanyID ,
                                                    SUM(Qty) Qty
                                            FROM    ngoods.ngoods_DC_Bas_PLOrder
                                            WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                                                    AND CompanyID = '{UserInfo.UserCompanyId}'
                                            GROUP BY ShopID ,
                                                    MaterialID ,
                                                    ExtrSource ,
                                                    CompanyID
                                          ) c ON a.MaterialID = c.MaterialID
                                                 AND a.ShopID = c.ShopID
                                                 AND a.ExtrSource = c.ExtrSource
                                                 AND a.CompanyID = c.CompanyID
                                LEFT JOIN ( SELECT  *
                                            FROM    ngoods.ngoods_DC_SatisfyRateTable
                                            WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                                          ) d ON a.ShopID = d.ShopID
                                                 AND a.ExtrSource = d.ExtrSource
                                LEFT JOIN ( SELECT  *
                                            FROM    ngoods.ngoods_DC_ItemPayTable
                                            WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                                          ) e ON a.ShopID = e.ShopID
                                                 AND b.ItemID = e.ItemID
                                                 AND a.ExtrSource = e.ExtrSource
                      WHERE     a.TaskBillNo = '{t.TaskBillNo}'
                                AND a.MaterialID = '{t.MaterialID}'
                                AND a.ExtrSource = '{UserInfo.UserExtrSource}'
                                AND a.CompanyID = '{UserInfo.UserCompanyId}'
                    ) a
        ) a   
) AS NUM
WHERE   SRD.ExtrSource = '{UserInfo.UserExtrSource}'
        AND SRD.CompanyID = '{UserInfo.UserCompanyId}'
        AND SRD.TaskBillNo = NUM.TaskBillNo
        AND SRD.MaterialID = NUM.MaterialID
        AND SRD.ShopID = NUM.ShopID
        AND SRD.ExtrSource = NUM.ExtrSource
        AND SRD.CompanyID = NUM.CompanyID;");
                    }
                }
                var cmd = Helper.GetSqlStringCommand(sb.ToString());
                return Helper.ExecuteNonQuery(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        //作者：曹亚亮
        //时间：2017-06-07
        /// <summary>
        /// 根据任务单号获取累计更新率
        /// </summary>
        /// <param name="TaskBillNo">任务单号</param>
        /// <returns></returns>
        public DataTable GetUpdateRate(string TaskBillNo)
        {
            try
            {
                var sql = $@"SELECT  TaskBillNo ,
        MaterialID ,
        MAX(UpdateRate) UpdateRate
FROM    ( SELECT    a.ExtrSource ,
                    a.CompanyID ,
                    a.Yearv ,
                    a.ParentAreaID ,
                    b.TaskBillNo ,
                    b.MaterialID ,
                    SUM(UpdateRate) AS UpdateRate
          FROM      ngoods.ngoods_PL_Bas_SalePlan a
                    INNER JOIN ( SELECT a.ExtrSource ,
                                        a.CompanyID ,
                                        a.Yearv ,
                                        a.SeasonID ,
                                        a.ParentAreaID ,
                                        a.Weekv ,
                                        b.TaskBillNo ,
                                        b.MaterialID
                                 FROM   ngoods.ngoods_PL_Bas_SalePlan a
                                        INNER JOIN ( SELECT a.TaskBillNo ,
                                                            a.MaterialID ,
                                                            a.CompanyID ,
                                                            a.ExtrSource ,
                                                            C.ParentAreaID
                                                     FROM   ngoods.ngoods_DC_ShopReqDetail a
                                                            INNER JOIN ( SELECT
                                                              *
                                                              FROM
                                                              ndw.ndw_Shop
                                                              WHERE
                                                              ExtrSource = '{UserInfo.UserExtrSource}'
                                                              AND CompanyID = '{UserInfo.UserCompanyId}'
                                                              ) b ON a.ShopID = b.ShopID
                                                              AND a.ExtrSource = b.ExtrSource
                                                              AND a.CompanyID = b.CompanyID
                                                            INNER JOIN ( SELECT
                                                              *
                                                              FROM
                                                              ndw.ndw_AreaProCity
                                                              WHERE
                                                              ExtrSource = '{UserInfo.UserExtrSource}'
                                                              AND CompanyID = '{UserInfo.UserCompanyId}'
                                                              ) c ON b.AreaID = c.AreaID
                                                              AND a.ExtrSource = c.ExtrSource
                                                              AND a.CompanyID = c.CompanyID
                                                     WHERE  a.TaskBillNo = '{TaskBillNo}'
                                                            AND a.ExtrSource = '{UserInfo.UserExtrSource}'
                                                            AND a.CompanyID = '{UserInfo.UserCompanyId}'
                                                     GROUP BY a.TaskBillNo ,
                                                            a.MaterialID ,
                                                            a.CompanyID ,
                                                            a.ExtrSource ,
                                                            C.ParentAreaID
                                                   ) b ON a.ExtrSource = b.ExtrSource
                                                          AND a.CompanyID = b.CompanyID
                                                          AND a.ParentAreaID = b.ParentAreaID
                                                          AND a.Yearv = Year(CURRENT_DATE)
                                                          AND a.Weekv = week(CURRENT_DATE)+1
                                 WHERE  a.ExtrSource = '{UserInfo.UserExtrSource}'
                                        AND a.CompanyID = '{UserInfo.UserCompanyId}'
                               ) b ON a.ExtrSource = b.ExtrSource
                                      AND a.CompanyID = b.CompanyID
                                      AND a.Yearv = b.Yearv
                                      AND a.ParentAreaID = b.ParentAreaID
                                      AND a.SeasonID = b.SeasonID
                                      AND a.Weekv <= b.Weekv
          WHERE     a.ExtrSource = '{UserInfo.UserExtrSource}'
                    AND a.CompanyID = '{UserInfo.UserCompanyId}'
          GROUP BY  a.ExtrSource ,
                    a.CompanyID ,
                    a.Yearv ,
                    a.ParentAreaID ,
                    b.TaskBillNo ,
                    b.MaterialID
        ) a
GROUP BY TaskBillNo ,
        MaterialID  ";

                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        //作者：曹亚亮
        //时间：2017-06-07
        /// <summary>
        /// 获取累计更新率小于90%的店铺排序数 废弃暂时没有到
        /// </summary>
        /// <param name="TaskBillNo">任务单号</param>
        /// <param name="MaterialID">货号ID</param>
        /// <returns></returns>
        public DataTable GetMin90ShopSortNum(string TaskBillNo, string MaterialID)
        {
            try
            {
                var sql = $@"SELECT  a.TaskBillNo ,
        a.ShopID ,
        a.MaterialID ,
        ROW_NUMBER() OVER ( PARTITION BY a.TaskBillNo, a.MaterialID ORDER BY Qty DESC, PayAmount DESC ) AS RowNum
FROM    ( SELECT DISTINCT
                    a.TaskBillNo ,
                    a.ShopID ,
                    a.MaterialID ,
                    ISNULL(c.Qty, 0) AS Qty ,
                    b.ItemID ,
                    ISNULL(d.McRate, 0) AS McRate ,
                    ISNULL(e.PayAmount, 0) AS PayAmount
          FROM      ngoods.ngoods_DC_ShopReqDetail a
                    INNER JOIN ( SELECT *
                                 FROM   nods.nods_erp_SD_Mat_Material
                                 WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                               ) b ON a.MaterialID = b.MaterialID
                                      AND a.ExtrSource = b.ExtrSource
                    LEFT JOIN ( SELECT  ExtrSource ,
                                        CompanyID ,
                                        ShopID ,
                                        MaterialID ,
                                        SUM(Qty) Qty
                                FROM    ngoods.ngoods_DC_Bas_PLOrder
                                WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                                        AND CompanyID = '{UserInfo.UserCompanyId}'
                                GROUP BY ExtrSource ,
                                        CompanyID ,
                                        ShopID ,
                                        MaterialID
                              ) c ON a.MaterialID = c.MaterialID
                                     AND a.ShopID = c.ShopID
                                     AND a.ExtrSource = c.ExtrSource
                                     AND a.CompanyID = c.CompanyID
                    LEFT JOIN ( SELECT  *
                                FROM    ngoods.ngoods_DC_SatisfyRateTable
                                WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                              ) d ON a.ShopID = d.ShopID
                                     AND a.ExtrSource = d.ExtrSource
                    LEFT JOIN ( SELECT  *
                                FROM    ngoods.ngoods_DC_ItemPayTable
                                WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                              ) e ON a.ShopID = e.ShopID
                                     AND b.ItemID = e.ItemID
                                     AND a.ExtrSource = e.ExtrSource
          WHERE     a.TaskBillNo = '{TaskBillNo}'
                    AND a.MaterialID = '{MaterialID}'
                    AND a.ExtrSource = '{UserInfo.UserExtrSource}'
                    AND a.CompanyID = '{UserInfo.UserCompanyId}'
        ) A ";

                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        //作者：曹亚亮
        //时间：2017-06-07
        /// <summary>
        /// 获取累计更新率大于90%的店铺排序数 废弃暂时没有到
        /// </summary>
        /// <param name="TaskBillNo">任务单号</param>
        /// <param name="MaterialID">货号ID</param>
        /// <returns></returns>
        public DataTable GetMax90ShopSortNum(string TaskBillNo, string MaterialID)
        {
            try
            {
                var sql = $@"SELECT  TaskBillNo ,
        a.ShopID ,
        a.MaterialID ,
        ROW_NUMBER() OVER ( PARTITION BY a.TaskBillNo, a.MaterialID ORDER BY McFlag, PayFlag ) AS RowNum
FROM    ( SELECT    a.TaskBillNo ,
                    a.ShopID ,
                    a.MaterialID ,
                    McRate ,
                    PayAmount ,
                    CASE WHEN McRate <= 0.9 THEN 1
                         ELSE 2
                    END AS McFlag ,
                    CASE WHEN McRate <= 0.9
                         THEN ROW_NUMBER() OVER ( PARTITION BY a.TaskBillNo,
                                                  a.MaterialID,
                                                  CASE WHEN McRate <= 0.9
                                                       THEN 1
                                                       ELSE 2
                                                  END ORDER BY McRate, PayAmount DESC )
                         ELSE ROW_NUMBER() OVER ( PARTITION BY a.TaskBillNo,
                                                  a.MaterialID,
                                                  CASE WHEN McRate <= 0.9
                                                       THEN 1
                                                       ELSE 2
                                                  END ORDER BY PayAmount DESC )
                    END AS PayFlag
          FROM      ( SELECT DISTINCT
                                a.TaskBillNo ,
                                a.ShopID ,
                                a.MaterialID ,
                                ISNULL(c.Qty, 0) AS Qty ,
                                b.ItemID ,
                                ISNULL(d.McRate, 0) AS McRate ,
                                ISNULL(e.PayAmount, 0) AS PayAmount
                      FROM      ngoods.ngoods_DC_ShopReqDetail a
                                INNER JOIN ( SELECT *
                                             FROM   nods.nods_erp_SD_Mat_Material
                                             WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                           ) b ON a.MaterialID = b.MaterialID
                                                  AND a.ExtrSource = b.ExtrSource
                                LEFT JOIN ( SELECT  ExtrSource ,
                                                    CompanyID ,
                                                    ShopID ,
                                                    MaterialID ,
                                                    SUM(Qty) Qty
                                            FROM    ngoods.ngoods_DC_Bas_PLOrder
                                            WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                                                    AND CompanyID = '{UserInfo.UserCompanyId}'
                                            GROUP BY ExtrSource ,
                                                    CompanyID ,
                                                    ShopID ,
                                                    MaterialID
                                          ) c ON a.MaterialID = c.MaterialID
                                                 AND a.ShopID = c.ShopID
                                                 AND a.ExtrSource = c.ExtrSource
                                                 AND a.CompanyID = c.CompanyID
                                LEFT JOIN ( SELECT  *
                                            FROM    ngoods.ngoods_DC_SatisfyRateTable
                                            WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                                          ) d ON a.ShopID = d.ShopID
                                                 AND a.ExtrSource = d.ExtrSource
                                LEFT JOIN ( SELECT  *
                                            FROM    ngoods.ngoods_DC_ItemPayTable
                                            WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                                          ) e ON a.ShopID = e.ShopID
                                                 AND b.ItemID = e.ItemID
                                                 AND a.ExtrSource = e.ExtrSource
                      WHERE     a.TaskBillNo = '{TaskBillNo}'
                                AND a.MaterialID = '{MaterialID}'
                                AND a.ExtrSource = '{UserInfo.UserExtrSource}'
                                AND a.CompanyID = '{UserInfo.UserCompanyId}'
                    ) a
        ) a ";

                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }


        #endregion

        #region 抢货作业

        [MustLogin]
        [Description("获取所有的操作员ID编号和名称API")]
        [ReturnType(typeof(PaperData<PickingOperationsModel.GetOperatorModel>))]
        public ApiResult GetOperator()
        {
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var dt = Get_Erp_Sys_User();
                var list = ModelConvertHelper<PickingOperationsModel.GetOperatorModel>.ConvertToModel(dt);
                return Success(list);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        [MustLogin]
        [Description("根据任务单号和配货仓库ID获取抢货店铺需求API")]
        [ReturnType(typeof(PaperData<PickingOperationsModel.GetShopReqDetailModel>))]
        public ApiResult GetShopReqDetail([ParamDescription("任务单号")] string TaskBillNo, [ParamDescription("配货仓库ID")] string OutStockID, [ParamDescription("是否执行抢货1：否 2：是")] int Type = 1, [ParamDescription("页码")] int Page = 1, [ParamDescription("页数")] int Size = 10)
        {
            try
            {
                if (string.IsNullOrEmpty(TaskBillNo))
                {
                    return Error("任务单号不能为空");
                }
                if (string.IsNullOrEmpty(OutStockID))
                {
                    return Error("配货仓库ID不能为空");
                }
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                if (Type == 2)//执行抢货逻辑 -抢货逻辑脚本出来后需要修改
                {
                    var strSuccess = ScrambleLogic(TaskBillNo, OutStockID);
                    if (strSuccess != "")
                    {
                        return Error(strSuccess);
                    }
                    else
                    {
                        return Success(0);
                    }
                }

                var count = ReturnDC_GetShopReqDetailCount(TaskBillNo, OutStockID);
                var dt = DC_GetShopReqDetail(TaskBillNo, OutStockID, Page, Size);
                var result = PaperData<PickingOperationsModel.GetShopReqDetailModel>.Create(count, Size, Page);
                var list = ModelConvertHelper<PickingOperationsModel.GetShopReqDetailModel>.ConvertToModel(dt);
                result.Items = list;
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        [MustLogin]
        [JsonType]
        [Description("根据任务单号生成ERP到店铺的单据API")]
        [ReturnType(typeof(PaperData<PickingOperationsModel.GetErpShopOrderModel>))]
        public ApiResult GetErpShopOrder(PickingOperationsModel.SetErpShopOrderModel param)
        {
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                if (param == null) return Error("参数对象不能为空");
                if (string.IsNullOrEmpty(param.TaskBillNo)) return Error("任务单号不能为空");
                if (string.IsNullOrEmpty(param.Billstatus)) return Error("单据状态不能为空");
                if (string.IsNullOrEmpty(param.UserID)) return Error("操作员ID不能为空");
                if (string.IsNullOrEmpty(param.TaskType)) return Error("配货方式不能为空");

                var data = new PickingOperationsModel.GetErpShopOrderModel();
                var eom = new ErpOrderHelper.ErpOrderMessage();
                eom = DC_ErpShopOrder(param.TaskBillNo, param.Billstatus, param.UserID, param.TaskType, param.List, param.MaBillNo);
                data.List = null;
                data.Data = null;
                if (eom != null && eom.RetVal == 0)
                {
                    data.RetVal = eom.RetVal;
                    data.Message = "生成调拨申请单(未生效)成功";
                    data.List = ModelConvertHelper<PickingOperationsModel.EepBillNoModel>.ConvertToModel(eom.BillNo);

                    UpdateDC_TaskMaster(4, param.TaskBillNo);
                }
                else if (eom != null && eom.RetVal == 4)
                {
                    data.RetVal = eom.RetVal;
                    data.Message = "生成调拨申请单(生效)成功";
                    data.List = ModelConvertHelper<PickingOperationsModel.EepBillNoModel>.ConvertToModel(eom.BillNo);

                    UpdateDC_TaskMaster(3, param.TaskBillNo);
                }
                else if (eom != null && eom.RetVal == -2)
                {
                    data.RetVal = eom.RetVal;
                    data.Message = "以下货号可用库存不足，无法生成生效的调拨申请单";
                    data.Data = ModelConvertHelper<PickingOperationsModel.ErpErrDataModel>.ConvertToModel(eom.BillNo);
                }
                else if (eom != null && eom.RetVal == 5)
                {
                    data.RetVal = eom.RetVal;
                    data.Message = "该任务已经产生ERP单据，不可重复产生";
                }
                else if (eom != null && eom.RetVal == 6)
                {
                    data.RetVal = eom.RetVal;
                    data.Message = "该任务单号未进行抢货作业请先执行抢货";
                }
                else if (eom != null && eom.RetVal == 1)
                {
                    data.RetVal = eom.RetVal;
                    data.Message = "无有效数据，无法生成ERP单据";
                }
                else
                {
                    data.RetVal = -1;
                    data.Message = "生成ERP单据失败";
                }

                return Success(data);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        //作者：曹亚亮
        //时间：2017-06-10
        /// <summary>
        /// 获取操作员编码ID和名称
        /// </summary>
        /// <returns></returns>
        public DataTable Get_Erp_Sys_User()
        {
            try
            {
                var sql = $@"select distinct a.UserID,a.UserCode,a.UserName 
from nods.nods_erp_Sys_User a
inner join nods.nods_erp_Sys_User_Role b 
on  a.ExtrSource = b.ExtrSource and a.CompanyID = b.CompanyID 
and a.UserID=b.UserID and b.RoleID IN ('{UserInfo.UserExtrSource}00000013','{UserInfo.UserCompanyId}00000014')
where   a.ExtrSource = '{UserInfo.UserExtrSource}' and a.CompanyID = '{UserInfo.UserCompanyId}'
order by a.UserName";

                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        //作者：曹亚亮
        //时间：2017-07-26
        /// <summary>
        /// 根据任务单号和配货仓库ID获取抢货店铺需求行数
        /// </summary>
        /// <param name="TaskBillNo">任务单号</param>
        /// <param name="OutStockID">配货仓库ID</param>
        /// <returns>返回总行数</returns>
        public int ReturnDC_GetShopReqDetailCount(string TaskBillNo, string OutStockID)
        {
            try
            {
                var sql = $@"SELECT COUNT(1) 
FROM(
    SELECT SRD.CompanyID,SRD.ShopID,SRD.TaskBillNo,SRD.OutStockID,SRD.MaterialID,ROW_NUMBER()OVER(ORDER BY SRD.MaterialID,SRD.ShopNum,DDS.ShopName ) AS Row
    FROM ngoods.ngoods_DC_ShopReqDetail AS SRD
    LEFT JOIN ndw.ndw_Shop AS DDS ON SRD.ShopID=DDS.ShopID and DDS.CompanyID = SRD.CompanyID
    LEFT JOIN nods.nods_erp_Bas_Stock AS BS ON SRD.OutStockID=BS.StockID  and BS.CompanyID = SRD.CompanyID
    LEFT JOIN ndw.ndw_AreaProCity AS APC ON DDS.AreaID=APC.AreaID and APC.CompanyID = DDS.CompanyID
    LEFT JOIN nods.nods_erp_Bas_Stock AS EBS ON APC.StockID=EBS.StockID and EBS.CompanyID = APC.CompanyID
    LEFT JOIN nods.nods_erp_SD_Mat_Size AS SMS ON (SRD.SizeID = SMS.SizeID AND SMS.SizeName <> '2XL') and SMS.ExtrSource = SRD.ExtrSource
    LEFT JOIN ndw.ndw_SizeType AS ST ON APC.AreaID=ST.AreaID AND SMS.SizeID=ST.SizeID and ST.CompanyID = APC.CompanyID and SMS.ExtrSource = ST.ExtrSource
WHERE SRD.CompanyID = '{UserInfo.UserCompanyId}' AND SRD.TaskBillNo='{TaskBillNo}' AND SRD.OutStockID='{OutStockID}'  AND isnull(SRD.Qty,0)>0 
GROUP BY SRD.ShopNum,SRD.CompanyID,SRD.ShopID,DDS.ShopName,SRD.TaskBillNo,SRD.OutStockID,SRD.MaterialID
) CountRow";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Convert.ToInt32(Helper.ExecuteScalar(cmd));
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        //作者：曹亚亮
        //时间：2017-06-10
        /// <summary>
        /// 根据任务单号和配货仓库ID获取抢货店铺需求
        /// </summary>
        /// <param name="TaskBillNo">任务单号</param>
        /// <param name="OutStockID">配货仓库ID</param>
        /// <param name="Page">页码</param>
        /// <param name="Size">页数</param>
        /// <returns></returns>
        public DataTable DC_GetShopReqDetail(string TaskBillNo, string OutStockID, int Page, int Size)
        {
            try
            {
                var sql = $@"SELECT SRD.CompanyID,SRD.ShopID,DDS.ShopName,SRD.TaskBillNo,SRD.OutStockID,BS.StockName,SRD.MaterialID,SRD.SizeID,SMS.SizeName,SRD.Qty,SRD.ActualQty,SRD.FinishFlag,ST.SizeCoreFlag,ROW_NUMBER()OVER(ORDER BY SRD.MaterialID,SRD.ShopNum,DDS.ShopName,SMS.SizeCodeSort ) AS Row
FROM ngoods.ngoods_DC_ShopReqDetail AS SRD
LEFT JOIN ndw.ndw_Shop AS DDS ON SRD.ShopID=DDS.ShopID and SRD.CompanyID = DDS.CompanyID
LEFT JOIN nods.nods_erp_Bas_Stock AS BS ON SRD.OutStockID=BS.StockID and SRD.CompanyID = BS.CompanyID
LEFT JOIN ndw.ndw_AreaProCity AS APC ON DDS.AreaID=APC.AreaID and DDS.CompanyID = APC.CompanyID
LEFT JOIN nods.nods_erp_Bas_Stock AS EBS ON APC.StockID=EBS.StockID and APC.CompanyID = EBS.CompanyID
LEFT JOIN nods.nods_erp_SD_Mat_Size AS SMS ON (SRD.SizeID = SMS.SizeID AND SMS.SizeName <> '2XL') and SRD.ExtrSource = SMS.ExtrSource
LEFT JOIN ndw.ndw_SizeType AS ST ON APC.AreaID=ST.AreaID AND SMS.SizeID=ST.SizeID and APC.CompanyID = ST.CompanyID
INNER JOIN 
(
	SELECT * 
	FROM
	(
        SELECT SRD.CompanyID,SRD.ShopID,SRD.TaskBillNo,SRD.OutStockID,SRD.MaterialID,ROW_NUMBER()OVER(ORDER BY SRD.MaterialID,SRD.ShopNum,DDS.ShopName ) AS Row
        FROM ngoods.ngoods_DC_ShopReqDetail AS SRD
        LEFT JOIN ndw.ndw_Shop AS DDS ON SRD.ShopID=DDS.ShopID and SRD.CompanyID = DDS.CompanyID
        LEFT JOIN nods.nods_erp_Bas_Stock AS BS ON SRD.OutStockID=BS.StockID and BS.CompanyID = SRD.CompanyID
        LEFT JOIN ndw.ndw_AreaProCity AS APC ON DDS.AreaID=APC.AreaID and APC.CompanyID = DDS.CompanyID
        LEFT JOIN nods.nods_erp_Bas_Stock AS EBS ON APC.StockID=EBS.StockID and EBS.CompanyID = APC.CompanyID
        LEFT JOIN nods.nods_erp_SD_Mat_Size AS SMS ON (SRD.SizeID = SMS.SizeID AND SMS.SizeName <> '2XL') and SMS.ExtrSource = SRD.ExtrSource
        LEFT JOIN ndw.ndw_SizeType AS ST ON APC.AreaID=ST.AreaID AND SMS.SizeID=ST.SizeID and ST.ExtrSource = APC.ExtrSource and SMS.ExtrSource = ST.ExtrSource
        WHERE SRD.CompanyID = '{UserInfo.UserCompanyId}' and  SRD.TaskBillNo='{TaskBillNo}' AND SRD.OutStockID='{OutStockID}' AND isnull(SRD.Qty,0)>0 
        GROUP BY SRD.ShopNum,SRD.CompanyID,SRD.ShopID,DDS.ShopName,SRD.TaskBillNo,SRD.OutStockID,SRD.MaterialID
	)table1 where Row BETWEEN ({Page}-1)*{Size}+1 AND {Page}*{Size}
) GBC
 ON SRD.CompanyID=GBC.CompanyID AND SRD.ShopID=GBC.ShopID AND SRD.TaskBillNo=GBC.TaskBillNo AND SRD.OutStockID=GBC.OutStockID AND SRD.MaterialID=GBC.MaterialID
WHERE SRD.CompanyID = '{UserInfo.UserCompanyId}' AND SRD.TaskBillNo='{TaskBillNo}' AND SRD.OutStockID='{OutStockID}' AND isnull(SRD.Qty,0)>0 ";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }

        }

        //作者：曹亚亮
        //时间：2017-06-24
        /// <summary>
        /// 导出根据任务单号和配货仓库ID获取抢货店铺需求数
        /// </summary>
        /// <param name="Keys">导出条件</param>
        /// <returns></returns>
        public DataTable ExportDC_GetShopReqDetail(string Keys)
        {
            try
            {
                var sql = $@"SELECT  DDS.ShopCode ,
        DDS.ShopName ,
        SRD.MaterialID ,
        SMS.SizeName ,
        SRD.ActualQty
FROM    ngoods.ngoods_DC_ShopReqDetail AS SRD
        LEFT JOIN ( SELECT  *
                    FROM    ndw.ndw_Shop
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                  ) AS DDS ON SRD.ShopID = DDS.ShopID
                              AND SRD.ExtrSource = DDS.ExtrSource
                              AND SRD.CompanyID = DDS.CompanyID
        LEFT JOIN ( SELECT  *
                    FROM    nods.nods_erp_Bas_Stock
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                  ) AS BS ON SRD.OutStockID = BS.StockID
                             AND SRD.ExtrSource = BS.ExtrSource
                             AND SRD.CompanyID = BS.CompanyID
        LEFT JOIN ( SELECT  *
                    FROM    ndw.ndw_AreaProCity
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                  ) AS APC ON DDS.AreaID = APC.AreaID
                              AND SRD.ExtrSource = APC.ExtrSource
                              AND SRD.CompanyID = APC.CompanyID
        LEFT JOIN ( SELECT  *
                    FROM    nods.nods_erp_Bas_Stock
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                  ) AS EBS ON APC.StockID = EBS.StockID
                              AND SRD.ExtrSource = EBS.ExtrSource
                              AND SRD.CompanyID = EBS.CompanyID
        LEFT JOIN ( SELECT  *
                    FROM    nods.nods_erp_SD_Mat_Size
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                  ) AS SMS ON SRD.SizeID = SMS.SizeID
                              AND SMS.SizeName <> '2XL'
                              AND SRD.ExtrSource = SMS.ExtrSource
WHERE   SRD.ExtrSource = '{UserInfo.UserExtrSource}'
        AND SRD.CompanyID = '{UserInfo.UserCompanyId}'
{Keys}
ORDER BY DDS.ShopName ,
        SRD.MaterialID ,
        SMS.SizeCodeSort ";
                //--AND SRD.TaskBillNo = '{0}'
                //--AND SRD.OutStockID = '{1}'

                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        //作者：曹亚亮
        //时间：2017-06-14
        /// <summary>
        /// 调用抢货逻辑脚本函数
        /// </summary>
        /// <param name="TaskBillNo">任务单号</param>
        /// <param name="OutStockID">配货仓库ID</param>
        public string ScrambleLogic(string TaskBillNo, string OutStockID)
        {
            //修改抢货逻辑脚本
            try
            {
                var server = IpAddressHelper.WebConfig("python-server"); //"192.168.10.127";
                var username = IpAddressHelper.WebConfig("python-server-user"); //"bi";
                var port = Convert.ToInt32(IpAddressHelper.WebConfig("python-server-port")); //"22";
                var password = IpAddressHelper.WebConfig("python-server-pass"); //"Km_etl2017";
                var StrCommand = "/usr/bin/python  /home/bi/scripts/python/ngoods/picking/goods_DC_shop_hold.py \"" + TaskBillNo + "\" "+UserInfo.UserCompanyId+" "+ UserInfo.UserExtrSource + "";
                var result = ConnLinux(server, username, port, password, StrCommand);
                string strSuccess = result.Contains("success") ? "" : result;
                // /home/bi/scripts/python/noods/picking
                //var UpdateSql = string.Format(@"UPDATE goods.goods_DC_ShopReqDetail SET ActualQty=Qty WHERE TaskBillNo='{0}' AND OutStockID='{1}'", TaskBillNo, OutStockID);
                //var cmd = Helper.GetSqlStringCommand(UpdateSql);
                //int isNum = Helper.ExecuteNonQuery(cmd);

                if (strSuccess == "")
                {
                    var updateSql2 =
                        $@"UPDATE  ngoods.ngoods_DC_TaskMaster
SET     TaskFlag = 2 ,
        Finishdate = GETDATE()
WHERE   TaskBillNo = '{TaskBillNo}'
        AND StockID = '{OutStockID}'
        AND ExtrSource = '{UserInfo.UserExtrSource}'
        AND CompanyID = '{UserInfo.UserCompanyId}'";
                    var cmd2 = Helper.GetSqlStringCommand(updateSql2);
                    Helper.ExecuteNonQuery(cmd2);
                }
                return strSuccess;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //作者：曹亚亮
        //时间：2017-06-14
        /// <summary>
        /// 获取ERP到店铺的单据信息
        /// </summary>
        /// <param name="TaskBillNo">任务单号</param>
        /// <param name="Billstatus">单据状态</param>
        /// <param name="UserID">操作员ID</param>
        /// <param name="TaskType">配货方式:1可用库存 2成品外发到货通知单</param>
        /// <param name="ShopID">店铺内码</param>
        /// <param name="MaBillNo">手工单号</param>
        /// <returns></returns>
        public ErpOrderHelper.ErpOrderMessage DC_ErpShopOrder(string TaskBillNo, string Billstatus, string UserID, string TaskType, List<PickingOperationsModel.SetShopIDMaterialID> List, string MaBillNo)
        {
            try
            {
                var eom = new ErpOrderHelper.ErpOrderMessage();
                if (IsDC_ShopReqDetail_FinishFlag(TaskBillNo))
                {
                    eom.RetVal = 5; //该任务已经产生ERP单据，不可重复产生
                    eom.BillNo = null;
                    return eom;
                }
                else if (!IsDC_TaskMaster_TaskFlag(TaskBillNo))
                {
                    eom.RetVal = 6; //该任务单号未进行抢货作业请先执行抢货
                    eom.BillNo = null;
                    return eom;
                }
                else
                {
                    //修改根据店铺内码ID取数据2017-07-17
                    var strSqlWhere = string.Empty;
                    if (List != null && List.Count > 0)
                    {
                        foreach (var t in List)
                        {
                            strSqlWhere = strSqlWhere +
                                          $@" AND (ShopID<>'{t.ShopID}' OR MaterialID<>'{t.MaterialID}') ";
                        }
                    }


                    var dtInsert = ImportErpShopReqDetail(TaskBillNo, strSqlWhere);
                    if (dtInsert != null && dtInsert.Rows.Count > 0)
                    {
                        switch (UserInfo.UserExtrSource)
                        {
                            case "KM":
                                return KM_DC_ErpShopOrder(dtInsert, Billstatus, UserID, TaskType, MaBillNo, strSqlWhere);
                            default:
                                eom.RetVal = -1; //生成ERP单据失败
                                eom.BillNo = null;
                                return eom;
                        }
                    }
                    else
                    {
                        eom.RetVal = -1; //生成ERP单据失败
                        eom.BillNo = null;
                        return eom;
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        //作者：ljj
        //时间：2017-07-26
        public ErpOrderHelper.ErpOrderMessage KM_DC_ErpShopOrder(DataTable dt, string Billstatus, string UserID, string TaskType, string MaBillNo, string strSqlWhere)
        {
            try
            {
                var delSql = $@"DELETE  FROM KM_DC_ShopReqDetail
WHERE   CompanyID = '{UserInfo.UserCompanyId}'
        AND TaskBillNo = '{dt.Rows[0]["TaskBillNo"].ToString()}'
        AND ISNULL(FinishFlag, 0) = 0"; //先删SQL数据库数据
                var sb = new StringBuilder();
                for (var i = 0; i < dt.Rows.Count; i++)
                {
                    sb.AppendLine(
                        $@"INSERT INTO KM_DC_ShopReqDetail
        ( CompanyID ,
          TaskBillNo ,
          OutStockID ,
          ShopID ,
          StockID ,
          MaterialID ,
          SizeID ,
          Qty ,
          ActualQty
        )
VALUES  ( '{UserInfo.UserCompanyId}' ,
          '{dt.Rows[i]["TaskBillNo"].ToString()}' ,
          '{dt.Rows[i]["OutStockID"].ToString()}' ,
          '{dt.Rows[i]["ShopID"].ToString()}' ,
          '{dt.Rows[i]["StockID"].ToString()}' ,
          '{dt.Rows[i]["MaterialID"].ToString()}' ,
          '{dt.Rows[i]["SizeID"].ToString()}' ,
          {dt.Rows[i]["Qty"].ToString()} ,
          {dt.Rows[i]["ActualQty"].ToString()}
        );");
                }
                string procedureSql =
                    $@"exec [dbo].[KM_DC_MoveBill] '{UserInfo.UserCompanyId}','{dt.Rows[0]["TaskBillNo"].ToString()}','{Billstatus}','{UserID}','{TaskType}','{MaBillNo}' ";
                string updSql =
                    $@"UPDATE  ngoods.ngoods_DC_ShopReqDetail
SET     FinishFlag = 1
WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
        AND CompanyID = '{UserInfo.UserCompanyId}'
        AND TaskBillNo = '{dt.Rows[0]["TaskBillNo"].ToString()}'
        {strSqlWhere}
        AND ISNULL(FinishFlag, 0) = 0";
                return ErpOrderHelper.GetErpShopOrder_Task(delSql, sb.ToString(), procedureSql, updSql);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        //作者：曹亚亮
        //时间：2017-06-14
        /// <summary>
        ///  判断任务单号在店铺需求表是否已经生成ERP到店铺订单号
        /// </summary>
        /// <param name="TaskBillNo">任务单号</param>
        /// <returns></returns>
        public bool IsDC_ShopReqDetail_FinishFlag(string TaskBillNo)
        {
            try
            {
                var sql = $@"SELECT  COUNT(1)
FROM    ngoods.ngoods_DC_ShopReqDetail
WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
        AND CompanyID = '{UserInfo.UserCompanyId}'
        AND TaskBillNo = '{TaskBillNo}'
        AND ISNULL(FinishFlag, 0) = 1";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Convert.ToInt32(Helper.ExecuteScalar(cmd)) > 0;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        //作者：曹亚亮
        //时间：2017-06-14
        /// <summary>
        /// 判断任务单号在任务单号列表中的状态是否为以抢货
        /// </summary>
        /// <param name="TaskBillNo">任务单号</param>
        /// <returns></returns>
        public bool IsDC_TaskMaster_TaskFlag(string TaskBillNo)
        {
            try
            {
                var sql =
                    $@"SELECT  COUNT(1)
FROM    ngoods.ngoods_DC_TaskMaster
WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
        AND CompanyID = '{UserInfo.UserCompanyId}'
        AND TaskBillNo = '{TaskBillNo}'
        AND ISNULL(TaskFlag, 0) = 2 ";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Convert.ToInt32(Helper.ExecuteScalar(cmd)) > 0;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        //作者：曹亚亮
        //时间：2017-06-14
        /// <summary>
        /// 获取导入ERP的抢货店铺需求数据
        /// </summary>
        /// <param name="TaskBillNo">任务单号</param>
        /// <returns></returns>
        public DataTable ImportErpShopReqDetail(string TaskBillNo, string strSqlWhere)
        {
            try
            {
                var sql = $@"SELECT  CompanyID ,
        ExtrSource ,
        TaskBillNo ,
        OutStockID ,
        ShopID ,
        StockID ,
        MaterialID ,
        SizeID ,
        Qty ,
        ActualQty
FROM    ngoods.ngoods_DC_ShopReqDetail
WHERE   CompanyID = '{UserInfo.UserCompanyId}'
        AND ExtrSource = '{UserInfo.UserExtrSource}'
        AND TaskBillNo = '{TaskBillNo}'
        {strSqlWhere}
        AND ISNULL(FinishFlag, 0) = 0
        AND ISNULL(ActualQty, 0) > 0";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        //作者：曹亚亮
        //时间：2017-06-14
        /// <summary>
        /// 修改任务单表中的任务单号状态
        /// </summary>
        /// <param name="TaskFlag">状态值</param>
        /// <param name="TaskBillNo">任务单号</param>
        public void UpdateDC_TaskMaster(int TaskFlag, string TaskBillNo)
        {
            try
            {
                var UpdateSql =
                    $@"UPDATE  ngoods.ngoods_DC_TaskMaster
SET     TaskFlag = {TaskFlag} ,
        PushBillDate = GETDATE()
WHERE   TaskBillNo = '{TaskBillNo}'
        AND CompanyID = '{UserInfo.UserCompanyId}'
        AND ExtrSource = '{UserInfo.UserExtrSource}'";
                var cmd = Helper.GetSqlStringCommand(UpdateSql);
                Helper.ExecuteNonQuery(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion

        //作者：曹亚亮
        //时间：2017-05-15
        /// <summary>
        /// 根据单号和货号判断是否已经进行虚拟仓库分配
        /// </summary>
        /// <param name="BillNo">单号</param>
        /// <param name="MaterialID">货号</param>
        /// <returns>1：以分配 0：未分配</returns>
        public int IsAllot2(string BillNo, string MaterialID)
        {
            try
            {
                var sql =
                    $@"SELECT  COUNT(1)
FROM    ngoods.ngoods_DC_VirtualStock
WHERE   SourceBillNo = '{BillNo}'
        AND MaterialID = '{MaterialID}'
        AND ExtrSource = '{UserInfo.UserExtrSource}'
        AND CompanyID = '{UserInfo.UserCompanyId}'";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Convert.ToInt32(Helper.ExecuteScalar(cmd)) > 0 ? 1 : 0;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        [MustLogin]
        [Description("返回发货仓-配货作业")]
        [ReturnType(typeof(IList<FromStockModel>))]
        public ApiResult GetSendStockList()
        {
            try
            {
                IList<string> STOCKLIST = new List<string>
                {
                    "'GDC'",
                    "'XDC'",
                    "'ZDDC'",
                    "'YLC'",
                    "'SXC'",
                    "'TYC'",
                    "'ZZC'",
                    "'WHC'",
                    "'CDC'",
                    "'JBC'",
                    "'LNC'",
                    "'WH07'",
                    "'WH06'",
                    "'WH08'",
                    "'WH02'",
                    "'WH01'",
                    "'WHSX'",
                    "'WH05'",
                    "'WH09'",
                    "'WH04'",
                    "'WH03'",
                    "'WHSXC'",
                    "'XAC'",
                    "'HNC'"
                };
                var whereStr = "";
                switch (UserInfo.UserExtrSource)
                {
                    case "KM":
                        whereStr = $@" AND ( StockCode IN ( {string.Join(",", STOCKLIST)} )
              OR StockCode LIKE '%YW%'
            ) ";
                        break;
                    default:
                        break;
                }
                var sql = $@"SELECT  *
FROM    nods.nods_erp_Bas_Stock
WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
        AND CompanyID = '{UserInfo.UserCompanyId}'
        {whereStr}
ORDER BY StockCode"; //CompanyID = '{UserInfo.UserCompanyId}' and 
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                var result = ModelConvertHelper<FromStockModel>.ConvertToModel(dt);
                return Success(result);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

    }

    public class FromStockModel
    {
        [ParamDescription("仓库ID")]
        public string StockID { get; set; }
        [ParamDescription("仓库Code")]
        public string StockCode { get; set; }
        [ParamDescription("仓库")]
        public string StockName { get; set; }
    }
}
